UK vs US Date format when opening a form

[Raddle]

Hi

Could anyone give me a steer on how to tackle this little gem .. So my link-to-Excel-table of data, has UK date format, so the 4th August looks like this 04/08/2023.

My form provides a list of logins grouped by date and the data field on the form has this click event procedure:



DoCmd.OpenForm "frm_MainList", acFormDS, , "[Date] = #" & Me.Date & "#"

However when activated, say for 04/08/2023's logins, the db displays the list for 8th April!

Nice and no, I didn't spot it for while

Any hints on how to handle this?

Thank you so much

[Bob Fitz]

Allen Browne has some words of wisdom on dates: http://allenbrowne.com/ser-36.html

[Welshgasman]

I used this in some of my DBs' Your format in Excel would be dd/mm/yyyy, but like Access the date is actually stored as a number.

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

Just format your date value with strCJetDate

[Raddle]

Thanks both .. where do I need to put the Public const line ?

[CJ_London]

Personally I format using the the sql standard of "yyyy-mm-dd"

"[Date] = #" & format(Me.Date,"yyyy-mm-dd") & "#"

Note that Date is a reserved word (the function Date()) and should not be used as a field or control name. It can generate misleading error messages. Use a meaningful name such 'orderDate', 'searchDate', entryDate'

[Raddle]

Wow! Fab fab fab. Thank you so much. That nailed it although I am not sure why. Shouldn't it be dd-mm-yyyy?

But right now .. I don't care that much.

Thanks again

[Raddle]

Not sure how to mark this one as solved ... or does the admin do that?

[Minty]

yyyy-mm-dd is unambiguous, no matter what local or your regional settings say. When you are debugging strings you don't have to think which format it might be in.
Therefore if you have to do anything with dates, it's the best way to handle it. It is also the format SQL Server recognises, so is easier to just go with it in case you ever change the source database.

[Welshgasman]

Not sure how to mark this one as solved ... or does the admin do that?

Top right of the thread, might be marked Thread Tools?

[CJ_London]

That nailed it although I am not sure why. Shouldn't it be dd-mm-yyyy?

Dates are stored as numbers, for example today is
?cdbl(date())
45146
?cdbl(now())
45146.5799421296

the date is the bit before the decimal point and is the number of days since 31/12/1899 (so 1 is the 1st Jan 1900)
the bit after the decimal point is the number of seconds so far today divided by the number of seconds in a day (86400) - so 6am for example would be 0.25

what you see as dd/mm/yyyy is just a format of that number specified by your window settings.

SQL on the other hand will interpret a string on the basis of it's own standard of yyyy-mm-dd or the US format of mm/dd/yyyy

You are passing a string with your "[Date] = #" & Me.Date & "#" criteria

However sql is clever enough to know there are only 12 months in the year, so if you pass a value of say '31/07/2023' it will interpret that as dd/mm/yyyy. Which is probably what happened in your case - you started developing on or after 13th July and until you reached 1st August you would not realise you had a problem.

[Raddle]

Thank you all so much. Great day for learning 'stuff'

[Bob Fitz]

Thank you all so much. Great day for learning 'stuff'

Every day's a "School day"