CODEWITHSUNDEEP
vbams-access
ClydeL
ClydeL

Reputation: 13

IF statement in VBA that proceeded incorrectly

I have an Access VBA Function that sends a number of emails containing updated Excel spreadsheets, but only if the underlying data has been updated recently. This piece has been working just fine until last night when if seems that the IF statement incorrectly compared the system date to the data date and sent emails using the older data instead of sending a warning email to the person responsible for correcting the issue of having old data.

Access 2016 on a VM running Windows 10. The current data date is contained in a table and that date is correct. The system date is correct. Everything worked as it should except for comparing the dates. There were no failures or error codes generated.

Boiled down to the trouble spot the code reads ...

' check to see if data is out of date
If ERDBDate < Now() - 2 Then
   ' send notification of old data
Else
   ' send updated spreadsheets
End If

Although my experience in coding is not that great, I have never seen an IF statement improperly evaluate like this. All I can assume is either I am using a "shaky" method of comparing the dates or there was some kind of hickup on the server side that caused an incorrect system date to be returned (does this even seem possible?). I would rather it be me then not being able to rely on something as "simple" as the system date.

I have learned a tremendous amount from the answers and help posted on this site. Thanks for all your efforts!

Upvotes: 1

Views: 41

Answers (2)

Gustav
Gustav

Reputation: 55806

DateDiff is the proven method for comparing date and time. Or use Date() to have no time (with DateDiff however, that doesn't matter):

' check to see if data is out of date
If DateDiff("d", ERDBDate, Date) >= 2 Then
   ' send notification of old data
Else
   ' send updated spreadsheets
End If

Upvotes: 0

Vlado
Vlado

Reputation: 888

Try this statement: 

If ERDBDate < DateAdd("d", -2, Now()) then

Upvotes: 0

Related Questions