6 Replies Latest reply on Dec 18, 2011 6:07 PM by Olex

    Birthdays 21-28 days from CurrentDate

    Olex

      Title

      Birthdays 21-28 days from CurrentDate

      Post

      Hi everyone,

       

      I need to find Birthdays that are coming up between 21 and 28 days from CurrentDate.

       

      I have "Full Date Of Birth" field that displays... you guessed it, full date of birth. :)

       

      Then I created a new field "ThisYearBirthday" that was a calculation field (displayed as Date):

      GetAsDate ( Day ( Full Date Of Birth) & "/" & Month ( Full Date Of Birth ) & "/" & Year ( Get ( CurrentDate ) ) )

       

      Then I ran script that was searching in that field and included :

      Get ( CurrentDate ) + 21 & "..." & Get ( CurrentDate ) + 28

      calculation...

       

      Didn't have a problem with it, until I began getting into 2012... No birthdays would come up, only birthdays up to 31 December and that's it. :(...



      Then I tried changing script and enquire in "Full Date Of Birth" field and included:

       

      Let ( d = Get ( CurrentDate ) + 21 ; Substitute ( d ; Year ( d ) ; "*" ) )


      It worked fine - I could see Birthdays that were 21 days from now, however I need to find a range between 21 and 28 days from CurrentDate.



      And that's where I kinda stuck... Can't seems to understand how to add a range function to the above script line.

      Hope someone can help me out with this one

       

      Thanks

        • 1. Re: Birthdays 21-28 days from CurrentDate
          Sorbsbuster

          That's because a person with a birthday in early January will show in your calculation for ThisYearBirthday as having a birthday on 14/1/2011 all this year.  Their birthday in 2012 will only register as their 2012 birthday once you cross into 1/1/2012.

          An easier approach might be to use the the DayOfYear function.  You could have a field BirthdayDayOfYear = DayOfYear ( FullDateOfBirth ), and then search in that field for

          DayOfYear ( Get ( CurrentDate ) ) + 21 & "..." & DayOfYear ( Get ( CurrentDate ) ) + 28

          I assume you are only looking for a warning to plan for upcoming birthdays: note that it is only approximate as it will be thrown out by one day during leap years.  Or if you want to be really accurate you could accommodate that by checking if this year or the birth year was a leap year and adjusting.

          • 2. Re: Birthdays 21-28 days from CurrentDate
            Olex

            Thanks for your reply, Sorbsbuster.

             

            I tried creating "BirthdayDayOfYear" field and then putting the search for that field in the script but it returns with blank, no records found.

            Is there any way I can put "range search" in Let ( d = Get ( CurrentDate ) + 21 ; Substitute ( d ; Year ( d ) ; "*" ) ) formula?

            It seems working fine, the only downside to thei formula is that I can't add "range search"...

             

            Thank you for your time once again.

            • 3. Re: Birthdays 21-28 days from CurrentDate
              Sorbsbuster

              D'oh!  Sorry about that. The solution I suggested will suffer a similar effect.  (As you get near year end the DayRange will search for a range, say, 372...379, as Today + 21 exceeds 365.  You could use a Case Statment to correct for that, but here's an easier way.)

              You've almost answered your own question - you can use this to set a range:

              Date (

               Month ( Get ( CurrentDate ) )   ; Day ( Get ( CurrentDate ) ) + 21 ; Year ( Get ( CurrentDate ) )

              ) & "..." &

              Date (

               Month ( Get ( CurrentDate ) )   ; Day ( Get ( CurrentDate ) ) + 28 ; Year ( Get ( CurrentDate ) )

              )

              • 4. Re: Birthdays 21-28 days from CurrentDate
                Olex

                I tried the calculation you have posted - it didn't work,

                Even tried switching format (to suit Australian Date Fromat):

                Date ( Day ( Get ( CurrentDate ) )  + 21  ; Month ( Get ( CurrentDate ) ) ; Year ( Get ( CurrentDate ) ) ) & "..." & Date ( Day ( Get ( CurrentDate ) )  + 28  ; Month ( Get ( CurrentDate ) ) ; Year ( Get ( CurrentDate ) ) )

                 

                I even tried

                Date ( Day ( Get ( CurrentDate ) ) + 21  ; Month ( Get ( CurrentDate ) ) ; Year ( "*"  )) & "..." & Date ( Day ( Get ( CurrentDate ) ) + 28  ; Month ( Get ( CurrentDate ) ) ; Year ( "*"  ))


                This one just gave me all records in the database, so didn't help either. :(

                • 5. Re: Birthdays 21-28 days from CurrentDate
                  Sorbsbuster

                  Try this sample file (it also lets you modify the range from and to, though there's no error capture, etc.)  Please set the calculation on BirthdayNext to be UNSTORED when you do it.

                  http://www.4shared.com/file/XP0q32HE/BirthdayCheck.html

                  • 6. Re: Birthdays 21-28 days from CurrentDate
                    Olex

                    Thank you very much for your help and time, I'll give that calculation a go.