Date Comparison

[Galaxiom]

Please give me your final decision.

From the first response:

If Date > #12/31/2019# Then

The # is the date delimiter in Access.

 

[isladogs]

Kundan
You finally came back....
I agree. My first answer in post #2 is guaranteed to work.

 

[Kundan]

Kundan
You finally came back....
I agree. My first answer in post #2 is guaranteed to work.

Thanks, GOD BLESS YOU!!!!!!!!!!!!!

WHERE ARE THE SETTINGS TO ENABLE MACROS OF A DATABASE???

 

[isladogs]

File, Access options, Trust Center, Macro Settings

 

[Kundan]

Thanks. One problem with date is that on my system the date is in dd/mm/yy, when I write the code the date in hash automatically changes to mm/dd/yyyy.

If Format(DATE, "dd/mm/yyyy") = #12/23/2019# Then
MsgBox " HAPPY NEW YEAR ", vbExclamation, "Greetings:"
End If

Hence even on 24/12/2019 it is displaying the greeting. How to deal with it?
Is there any date formatting relating to the current DB which I can change?

 

[Galaxiom]

A date value is actually a number (the number of days since 30/12/1899).

The Date function returns a date value that represents today.

#12/23/2019# represents a date value.

Format(DATE, "dd/mm/yyyy") takes today's date value and returns a String.

Comparing strings against date values doesn't work.

Comparing strings that represent dates doesn't work unless they are formatted as yyyymmdd because they are compared in alphanumeric order.

30/12/2019 without delimiters will be arithmetically evaluated. (Thirty divided by 12 divided by 2019. It will be a very small number.

 

[Kundan]

Please tell me what changes need to be made to the above code in order to achieve the goal?

 

[arnelgp]

you may also use DateSerial() function:

If DATE() = DateSerial(2020, 1, 1) Then
MsgBox " HAPPY NEW YEAR ", vbExclamation, "Greetings:"
End If

 

[Gasman]

Please tell me what changes need to be made to the above code in order to achieve the goal?

In the UK we also use dd/mm/yyyy and I get this in the immediate window?

? date = #12/24/2019#
True

However you have to compare like with like.

However if I try what you posted today

? format(date,"dd/mm/yyyy") = #12/23/2019#
False

? date = #12/23/2019#
False

I get False?
Regardless, I'd expect you to use the same format in each, which you are not doing.

I have already suggested 01/01/2020 so that the format does not matter.
arnelgp has also done the same today.?

 

[isladogs]

Both are false because today isn't 23 Dec!!!

?format(Date,"mm/dd/yyyy")=#12/24/2019#
True

?Date=#12/24/2019#
True

 

[Gasman]

Both are false because today isn't 23 Dec!!!

?format(Date,"mm/dd/yyyy")=#12/24/2019#
True

?Date=#12/24/2019#
True

Yes I realise that, but Kundan posted that today?

 

[isladogs]

Yes I realise that, but Kundan posted that today?

Yes I realise that, and I thought that was the point he was making...that he expected that code to be false and not show the greeting.

Kundan
Your expression doesn't show the greeting on my system.
However, as previously recommended, I suggest you use one of these expressions

If Date > #12/31/2019# Then
MsgBox " HAPPY NEW YEAR ", vbExclamation, "Greetings:"
End If

Or

If Date >= #1/1/2020# Then
MsgBox " HAPPY NEW YEAR ", vbExclamation, "Greetings:"
End If

 

[Kundan]

Thanks, it worked. GOD BLESS YOU!!!!!!!!!!!! Will it work on any PC having different date format? Also if I want to change the format of #12/31/2019# to dd/mm/yy how do I do it?

 

[isladogs]

Remember dates are actually stored as double number datatype.
The formatting has no effect on the stored date.

Whilst the query designer will correctly understand your regional date format, VBA won't.

The supplied code is using the U.S. date format as required by VBA.
Yes. The code will work whatever regional date format you have.
For example, UK dates are dd/mm/yyyy but the code still works.

You cannot use dd/mm/yy in the code. If you try it will be automatically converted to the best 'guess' mm/dd/yyyy equivalent and it will often get it wrong

For example, type 'If Date>#31/12/19# Then' in the VBE.
It will be rewritten as 'If Date > #12/19/1931# Then'

So, to repeat, when working with dates using code, dates must be in one of the accepted formats.
Either mm/dd/yyyy ...or you can use yyyy-mm-dd

 

[namliam]

well real problem here relates back to using and relying on implicit conversions.

?format(Date,"mm/dd/yyyy")=#12/24/2019#

Here your changing from string to date and/or reverse. In a more strickt setting you should get an error comparign a string to a date value.
Instead VBA tries to help you converting the date to a string and because it is american software it is assuming american date format.

Implicit conversions are the devil and should be avoided !

 

[Kundan]

Thanks a lot. GOD BLESS YOU!!!!!!!!!!