My Assistant
Site Message (Message will auto close in 2 seconds)
Custom Search
|
  |
 Math On A Date Regardless Of Year, Access 2016 |
 Aug 20 2019, 12:50 PM Post#1 | |
Posts: 68 Joined: 18-December 18  | 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: CODE 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) -------------------- "If you don't make mistakes, you're not working on hard enough problems." -Frank Wilczek "Success is getting what you want. Happiness is wanting what you get." -B.R. Hayden Virtue is sufficient for happiness, therefore a sage is immune to misfortune. |
 |  |
| Aug 20 2019, 01:01 PM Post#2 | |
 UtterAccess VIP Posts: 22,228 Joined: 8-January 07 From: St. Catharines, ON (Canada) | 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... CODE If Abs(DateDiff("d", Date(), DateSerial(Year(Date()), Month(Birthdate), Day(Birthdate))) < 2 Then -------------------- Doug Steele, Microsoft Access MVP (2000-2018) Personal webpage Microsoft profile Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional |
| |
| Aug 20 2019, 01:01 PM Post#3 | |
 UA Moderator Posts: 76,880 Joined: 19-June 07 From: SunnySandyEggo | Hi. You can construct a date value using the DateSerial() function. You can then use the results to compare between dates. -------------------- Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas Access Website | Access Blog | Email |
| |
| Aug 20 2019, 02:42 PM Post#4 | |
Posts: 68 Joined: 18-December 18 | ah, brilliant! Thanks gentlemen. And double thanks Doug for the ABS reminder - I was about to handle the negative much less elegantly. -------------------- "If you don't make mistakes, you're not working on hard enough problems." -Frank Wilczek "Success is getting what you want. Happiness is wanting what you get." -B.R. Hayden Virtue is sufficient for happiness, therefore a sage is immune to misfortune. |
| |
|
Custom Search
|
 | Search Top Lo-Fi | 12th December 2019 - 05:35 AM |