Date Comparison (1 Viewer)

Micron

AWF VIP
Local time
Today, 11:20
Joined
Oct 20, 2018
Messages
3,476
The terms Most Significant digit and Least Significant Digit are well defined.
If someone asks when you were born would the year or the day be more significant?
I would say "December 20th, Nineteen Eighty (not my real birthdate). You're welcome to state it your way.

Look, this horse is dead. You're on about significant date portions and irrational date formats but to what end? If it makes you happy, you're absolutely right. One portion is more significant than the other and Bill Gates is a putz for not adopting the UK format. I'm unsubscribing - the last word will be yours.
 

isladogs

MVP / VIP
Local time
Today, 15:20
Joined
Jan 14, 2017
Messages
18,186
Kundan
You finally came back....
I agree. My first answer in post #2 is guaranteed to work.
 

Kundan

Registered User.
Local time
Today, 08:20
Joined
Mar 23, 2019
Messages
118
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

MVP / VIP
Local time
Today, 15:20
Joined
Jan 14, 2017
Messages
18,186
File, Access options, Trust Center, Macro Settings
 

Kundan

Registered User.
Local time
Today, 08:20
Joined
Mar 23, 2019
Messages
118
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?
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:20
Joined
Jan 20, 2009
Messages
12,849
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

Registered User.
Local time
Today, 08:20
Joined
Mar 23, 2019
Messages
118
Please tell me what changes need to be made to the above code in order to achieve the goal?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:20
Joined
May 7, 2009
Messages
19,169
you may also use DateSerial() function:

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

Gasman

Enthusiastic Amateur
Local time
Today, 15:20
Joined
Sep 21, 2011
Messages
14,044
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?

Code:
? date = #12/24/2019#
True

However you have to compare like with like.:confused:

However if I try what you posted today
Code:
? 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

MVP / VIP
Local time
Today, 15:20
Joined
Jan 14, 2017
Messages
18,186
Both are false because today isn't 23 Dec!!!

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

?Date=#12/24/2019#
True
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:20
Joined
Sep 21, 2011
Messages
14,044
Both are false because today isn't 23 Dec!!!

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

?Date=#12/24/2019#
True

Yes I realise that, but Kundan posted that today?
 

isladogs

MVP / VIP
Local time
Today, 15:20
Joined
Jan 14, 2017
Messages
18,186
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

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

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

Kundan

Registered User.
Local time
Today, 08:20
Joined
Mar 23, 2019
Messages
118
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

MVP / VIP
Local time
Today, 15:20
Joined
Jan 14, 2017
Messages
18,186
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
 
Last edited:

namliam

The Mailman - AWF VIP
Local time
Today, 16:20
Joined
Aug 11, 2003
Messages
11,696
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 !
 

Users who are viewing this thread

Top Bottom