Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Date + Time _ Math On A Date Regardless Of Year

Posted by: wheeledgoat Aug 20 2019, 12:50 PM

I think I understand dates in Access correctly that the date is actually stored as days since midnight on December 30, 1899.

I haven't tested this yet but I think it would work to check if today is somebody's birthday by formatting the date and seeing if they're equal:

If Format(dob, "mmdd") = Format(Now, "mmdd") Then

...but how would I perform math on a date to see if we're within a day of someone's birthday? (i.e. if their birthday was yesterday or tomorrow)

Posted by: Doug Steele Aug 20 2019, 01:01 PM

If you've got their birthday in a field named Birthdate, you can determine when their birthday is this year using DateSerial(Year(Date()), Month(Birthdate), Day(Birthdate))

Once you've got that, use the DateDiff function to compare this year's birthday to the current date. Don't forget to use Abs, since the difference may be negative...

  If Abs(DateDiff("d", Date(), DateSerial(Year(Date()), Month(Birthdate), Day(Birthdate))) < 2 Then

Posted by: theDBguy Aug 20 2019, 01:01 PM

Hi. You can construct a date value using the DateSerial() function. You can then use the results to compare between dates.

Posted by: wheeledgoat Aug 20 2019, 02:42 PM

ah, brilliant! Thanks gentlemen. And double thanks Doug for the ABS reminder - I was about to handle the negative much less elegantly.