Menu Close

Compare dates in MSAccess VBA

Comparing dates in VBA can be a frustrating thing. Due to the way dates are stored and the localization differences for each country.

Let’s assume we are in the Netherlands and today is October 31st 2019. If we open the Visual Basic for Applications application and type the following in the direct screen:

? Now()=DateValue("31-10-2019")

It returns False???

If we want to check if date 31-10-2023 is bigger than today (31-10-2019) it also fails:

If DateValue("31-10-2023") >= Now() Then
    Debug.print "Input date is bigger for sure"
Else
    Debug.print "But it will fail and show this message"
End If

What the heck??

We have two issues here we need to address. First we need to format the right date to compare with the right formatting and second we need to compare the right date values.

About the double precision and integer

Dates are stored as double-precision numbers. The Now() function returns a double-precision number for date and time but the DateValue() function returns an integer number for date only.

About the localization

31 10 2019 / 2019 10 31 / 10 31 2019 is easy to understand that this is October 31st 2019. But when you use 02 10 2019 or 2019 10 02. Which one is October 2nd and which one is February 10th?

It all depends on your localization settings in your user environment and the operating system underneath. Especially when you are using terminal server desktop you can have different user environment localization settings than the operating system itself. To make sure you use the right formatting of the todays date we can use the format() function.

Solution

Because we compare a date we will replace the now() function with date(). We use DateSerial() to extract the Year, Month and Day for today and with Format() we will place them in the right order as we use in our database. In the end we use DateValue to transform the input to an integer and then we compare the two dates to check if the stored date in the database is bigger than today. The result is a big line of code which will never fail regardless of the date formatting or any localization setting:

' rst!enddate = '31-10-2023'
' today = '31-10-2019'

If DateValue(Format(rst!enddate, "dd-mm-yyyy")) >= DateValue(Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd-mm-yyyy", vbMonday, vbFirstJan1)) Then
    Debug.print "Yes, stored date is still later than today"
End If

Keep pushing!

Related Posts

Leave a Reply

Your email address will not be published.