Date Comparison (1 Viewer)

[Kundan]

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?

 

[isladogs]

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

If Date > #12/31/2019# Then

Or

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

 

[CJ_London]

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

 

[isladogs]

Because I wasn't fully awake when I replied, I tested both methods first.
Both work as can be seen from the immediate window

?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

?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:

?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

 

[Gasman]

Use 01/01/2020 and >= and be done with it.

 

[Galaxiom]

2. In your code you need to use an unambiguous date format such as mm/dd/yyyy

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"

 

[isladogs]

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

?#29/02/19#
19/02/2029

Even leap years are wrongly interpreted

?#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

 

[Galaxiom]

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.

Not just Access. It is a Windows "feature".

 

[Micron]

completely irrational US date format.

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 Hartman]

NEVER Format() a date before you use it in a compare. The Format() function converts dates to strings so you will get a string compare rather than a date compare.

Also, as someone already pointed out, the problem may be that you used "" rather than ## ad the delimiter so that could skew the results.

Given that your standard date format is dd/mm/yy and 31/12/19 is completely ambiguous, you might want to use a four digit year plus in order to not hardcode the date test every year, you might want to either use a stored LastLoggedIn date or just say happy new year for the first week or 10 days of January in which case you would subtract the current date from 1/1/Year(Date()) and if it was <= 10 or 7 or whatever range you want, send the greeting.

 

[isladogs]

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.

 

[Galaxiom]

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.

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?

 

[Pat Hartman]

No one asked my opinion of how dates should be formatted. In my mainframe days, we ALWAYS stored dates as yyyymmdd OR yyyyddd so we could compare and sort without having to format anything.

Dates are formatted for the convenience of humans. Internally in Access and other RDBMS, the dates are stored as double precision numbers and NEVER formatted prior to performing any operation. Each implementation has a different origin date. For Access, the zero date is 12/30/1899. For SQL Server I think it might be 1/1/1900. To me, it makes sense to have the origin date 12/31/1899. I have no idea why each team chose what they chose. Dates should only be formatted when needed for human consumption or when working with SQL strings.

That said, SQL Server ASSUMES mm/dd/yyyy to be the standard format. Again, no one consulted me so for anyone in the world where your default date format is not month, day, year, you have to be aware of the issue and Format() your STRING dates to mm/dd/yyyy or yyyy/mm/dd when passing them to SQL but ONLY format when working with a string.

Select ...
From ...
Where MyDate > Forms!yourform!yourdate
OR
Select ...
From ...
Where MyDate > SomeOtherDateField

Work fine as stored querydefs. Access knows yourdate and SomeOtherDateField are a date data type and that's what it passes to the query rather than a formatted string.
However, if you were to build this exact same query in VBA, you MUST format the date yourself

strSQL = strSQL & " Where MyDate > #" Format(Forms!yourform!yourdate, yyyy/mm/dd) & "#"

There is a certain logic to this distinction once you understand the issue and that in the first example you are referencing objects but in the second, you are working with a string.

 

[Micron]

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?

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.

 

[isladogs]

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.

 

[Micron]

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.

 

[isladogs]

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.

 

[Micron]

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."

 

[isladogs]

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

 

[Galaxiom]

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?