Date Comparison

K

Kundan

Registered User.
Local time
Today, 06:11
Joined
Mar 23, 2019
Messages
118
I have the following code on the Onload event of an opening form:
If (DATE > "31-12-19") Then
MsgBox " HAPPY NEW YEAR ", vbExclamation, "Greetings:"

But it has already started giving greetings on some PCs of the local n/w. This may be due to the different date formats on those PCs. How to solve this problem so that all receive greetings in the new year irrespective of their date formats?
 
1. You should use # for date delimiters not "
2. In your code you need to use an unambiguous date format such as mm/dd/yyyy

Code: 
If Date > #12/31/2019# Then
Or
Code: 
If Format(Date, "mm/dd/yyyy") > #12/31/2019# Then
 
not tested, but not sure

If Format(Date, "mm/dd/yyyy") > #12/31/2019# Then

will work since the format function changes the date to a string

my suggestion would be

If Format(Date, "yyyymmdd") > "20191231" Then
 
Because I wasn't fully awake when I replied, I tested both methods first.
Both work as can be seen from the immediate window

Code: 
?Date > #12/31/2019#
False
?Format(Date, "mm/dd/yyyy") > #12/31/2019#
False

?Date < #12/31/2019#
True
?Format(Date, "mm/dd/yyyy") < #12/31/2019#
True

Your suggestion also works
Code: 
?Format(Date, "mm/dd/yyyy") > "12/31/2019"
False

?Format(Date, "mm/dd/yyyy") < "12/31/2019"
True

I had checked by altering the system date to a potentially ambiguous UK date 09/12/2019 (9 Dec)
For completeness here are those results:
Code: 
?Date
09/12/2019 

?Date>#12/11/2019#
False
?Format(Date,"mm/dd/yyyy")>#12/11/2019#
False
?Format(Date,"mm/dd/yyyy")>"12/11/2019"
False

?Date<#12/11/2019#
True
?Format(Date,"mm/dd/yyyy")<#12/11/2019#
True
?Format(Date,"mm/dd/yyyy")<"12/11/2019"
True
 
Last edited:
Use 01/01/2020 and >= and be done with it. :D
 
isladogs said:
2. In your code you need to use an unambiguous date format such as mm/dd/yyyy
Click to expand...

Although above dd/mm/yyyy in the the defaults there is nothing "unambiguous" about the completely irrational US date format.

yyyy-mm-dd is unambiguous.

Windows will accept almost anything it can interpret as a date if you give it half a chance.

Test this string as a date and see what you get:

"29/02/19"
 
Hi Greg
I had planned to mention yyyy-mm-dd but didn't want to add too many suggestions for the OP to try

For anyone who isn't aware Access will interpret dates which use two digits for the year such as 29/02/19 as a valid date where possible.
The results can be surprising

Code: 
?#29/02/19#
19/02/2029
Even leap years are wrongly interpreted
Code: 
?#29/02/20#
20/02/2029

Best to always use 4 digits for years as well as formatting as mm/dd/yyyy or yyyy-mm-dd
 
isladogs said:
For anyone who isn't aware Access will interpret dates which use two digits for the year such as 29/02/19 as a valid date where possible.
Click to expand...

Not just Access. It is a Windows "feature".
 
completely irrational US date format.
Click to expand...
What's irrational about it? There was a time when 2 digit year was all you could use so there needed to be a consistent application of some rule as to which parts of a date were going to be slotted where. If Bill Gates had been a Brit the rule would be the other way. When you make the product, you make the rules.
 
Pat
Of course, I was well aware before writing post #2 that formatting dates results in a text string.
However, as already stated, the outcomes work reliably as I showed in post #4.
If you can come up with examples that fail, do please provide them.

Kundan
Are you actively following this thread as you haven't returned since post #1.
 
Micron said:
What's irrational about it? There was a time when 2 digit year was all you could use so there needed to be a consistent application of some rule as to which parts of a date were going to be slotted where. If Bill Gates had been a Brit the rule would be the other way. When you make the product, you make the rules.
Click to expand...

The irrationality of US date format is perfectly obvious to everyone except those from the US.

What other numeric expression puts the Least Significant Digits in the middle?
 
Galaxiom said:
The irrationality of US date format is perfectly obvious to everyone except those from the US.
What other numeric expression puts the Least Significant Digits in the middle?
Click to expand...
You are certainly entitled to your bias, which is derived from your origins, no doubt. I'm not from the US and prefer Month Day Year format only because that's what I'm used to. What makes the day less significant than the month or year? If someone asks you what the date is, do you actually say "it's the 20th of December? Or do you say "it's December the 20th." Just asking what the norm is in the UK when it's expressed vocally.
 
In the UK, I think most of us would usually say - 20th December. However for info Galaxiom is from Australia

Whilst Galaxiom can, and I hope will, answer the point about significance for himself, one reason why I would also state that is that a day is a more limited period of time than either a month or a year.
Hence, in an Access database with date field there will be less records for an individual date than for a month and both will be less than that for a year.

So logically either yyyy-mm-dd (most to least significant) or dd/mm/yyyy (least to most) makes more sense to me.
 
Last edited:
Regardless, I don't think it has anything to do with why the US date format was adopted, nor do I believe databases ever entered into the decision. If I'm wrong, then by all means anyone should provide a link to evidence of that. As I stated, I suspect the decision about Windows date format was governed by the 'standard' in use by its creator. If BG had been a Brit (or maybe Aussie) then it would be the other way. That the de facto format isn't the standard for some people doesn't make it irrational IMHO. That was my point.
 
Not disagreeing with any of that.
Of course the default date format was chosen because the decision was made by a US company
You asked why the day part of the date was least significant and I answered that.
 
So is it still least significant if a table has say, 1,000 records for any given day? Not sure I see the connection between the placement of the day digits as being any more or less significant than any other part of the date. To me, the date is a composite value and no part is more or less significant unless maybe the records dictate that. If that were true, I suspect cases can be made for making the day number more or less important in the records.

As Forrest Gump once said, "That's all I've got to say about that."
 
If there are 1000 records on 01/11/2019 and 200 the next day with similar numbers the following month there would be more data in each month than any individual day and more again for the year as a whole.

Anyway, I think we're flogging a dead horse here. Or to put it another way, I'm bored now :D
 
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?
 
Galaxiom said:
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?
Click to expand...
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.
 
You must log in or register to reply here.

Similar threads

D
Solved Date is displaying odd behavior
Replies
14
Views
687
Ken Sheridan
Ken Sheridan
A
FILTER PRINT REPORT
Replies
2
Views
458
arnelgp
arnelgp
C
Solved problem opening a .csv file in excel - dates all messed up
2
Replies
23
Views
2,644
CJ_London
C
A
Filter Records
Replies
1
Views
596
June7
June7
A
Solved VBA code for deleting Outlook 'Appointments' lying within a specified date range
Replies
8
Views
1,259
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom