UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Math On A Date Regardless Of Year, Access 2016    
 
   
wheeledgoat
post Aug 20 2019, 12:50 PM
Post#1



Posts: 67
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.
Go to the top of the page
 
Doug Steele
post Aug 20 2019, 01:01 PM
Post#2


UtterAccess VIP
Posts: 22,192
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
Go to the top of the page
 
theDBguy
post Aug 20 2019, 01:01 PM
Post#3


Access Wiki and Forums Moderator
Posts: 76,316
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
Go to the top of the page
 
wheeledgoat
post Aug 20 2019, 02:42 PM
Post#4



Posts: 67
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.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st September 2019 - 08:49 AM