Date Comparison (1 Viewer)

Micron

AWF VIP
Joined
Oct 20, 2018
Messages
1,745
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

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,327
Kundan
You finally came back....
I agree. My first answer in post #2 is guaranteed to work.
 

Kundan

Registered User
Joined
Mar 23, 2019
Messages
78
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
Joined
Jan 20, 2009
Messages
11,817
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
Joined
Mar 23, 2019
Messages
78
Please tell me what changes need to be made to the above code in order to achieve the goal?
 

arnelgp

error reading drive A:
Joined
May 7, 2009
Messages
8,623
you may also use DateSerial() function:

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

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,016
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

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,327
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
Joined
Sep 21, 2011
Messages
5,016
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

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,327
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
Joined
Mar 23, 2019
Messages
78
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

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,327
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
Joined
Aug 11, 2003
Messages
11,491
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 (Users: 0, Guests: 1)

Top Bottom