short date in a table

[Michael.Koppelgaard]

Using VBA I put date values into a table with this input mask :
Format(Me.Txt_dato_ind, "dd-mm-yyyy")
The date field in the table are formated to short date. But it seemes that the date are recorded into the fields with month before day: mm/dd/yyyy.
The general format for my computer are dd/mm/yyyy.
How can I change the existing date in the table?
And how can I make future recordings correct?

Best Michael

 

[arnelgp]

bring your table in Design view.
click on the Date field and below Property Sheet:

Format: dd-mm-yyyy

 

[Minty]

Don't format dates or anything else directly in Tables, just do it on the forms they interact with.
What is your whole code for the insert? Using format can force the date to a string.

Review this and bookmark it!

Microsoft Access tips: International Dates in Access

How to ensure dates are interpreted correctly in a Microsoft Access database, even when the user's regional settings are different from the US format.

allenbrowne.com

 

[Michael.Koppelgaard]

Thank for the reply:

My code is here:

DoCmd.RunSQL "INSERT INTO tb_LagerUdenNummereredeKasser ( PlaceringID, AnsvarligVipID, AnsvarligTapID, AktNr, IndsatDato, FjernesDato, Prøvebeskrivelse )" _
& "SELECT " & PlaceringID & "," & Me.Combo_VIP.Column(0) & "," & Me.Combo_TAP.Column(0) & ",'" & Me.Txt_projekt & "',#" & Format(Me.Txt_dato_ind, "dd-mm-yyyy") & "#,#" & Format(Me.Txt_dato_ud, "dd-mm-yyyy") & "#,'" & Me.Txt_beskrivelse & "';"

It´s a bit annoying that the format in the table is wrong. When I sort the table according to the date it´s sorted wrongly. The last date is 08/12/2022 and even though there a date of today 7. September 2022

 

[arnelgp]

ms Access only Save your date in English format.
you have incorrect date because of this:

Format(Me.Txt_dato_ind, "dd-mm-yyyy")


you need to format it in US, English date:

Format(Me.Txt_dato_ind, "mm/dd/yyyy")

 

[Minty]

If you read the Allen Browne article it tells you cause the problem.

As @arnelgp has also mentioned you have to use either American format or as I prefer, the unambiguous SQL Server format "yyyy-mm-dd" when you execute a SQL statement outside of the query editor.

When you debug these things it's much easier to see and read the date in the SQL Server format if you regulalrly have to concatenate them.

 

[Michael.Koppelgaard]

Thank you for the tip and article. "yyyy-mm-dd" did the trick

 

[Michael.Koppelgaard]

It is weird though that when you sort dates in American format (newest first) its sorted, as if they were in the format dd-mm-yyyy..
Than means that 12/08/2022 (8. December) comes before 08/12/2022 (12. August)

 

[CJ_London]

Probably also worth clarifying that date fields is stored as a decimal number. The bit before the decimal point being the number of days since 31st Dec 1899 and the bit after the time as of the number of second for the day divided by 86400 (number of seconds in a day). The format you see is just a format of that value based on local settings for 'human consumption'

?date()
08/09/2022
?cdbl(date())
44812
?now()
08/09/2022 08:32:15
?cdbl(now())
44812.3557407407

and to convert to a formatted date string (the one second difference is due to timing running the above two lines)
?format(44812.3557407407 ,"dd/mm/yyyy hh:mm:ss")
08/09/2022 08:32:16

and US format
?format(44812.3557407407 ,"mm/dd/yyyy hh:mm:ss")
09/08/2022 08:32:16


and for a date value as you are used to seeing it based on your local settings
?cdate(44812.3557407407)
08/09/2022 08:32:16

it is weird though that when you sort dates in American format (newest first) its sorted, as if they were in the format dd-mm-yyyy..
Than means that 12/08/2022 (8. December) comes before 08/12/2022 (12. August)

looks to me like you are sorting a text string, not the date value.

SQL will do it's best to get it right - with the assumption of mm/dd/yyyy. But if a value is 31/12/2022 it knows there are not 31 months in the year so treats it as dd/mm/yyyy. So unless you take the appropriate action to make the value unambiguous, and date with a day of 12 or less will be treated as mm/dd/yyyy, any day over 12 will be treated as dd/mm/yyyy.

As with Minty, I use the sql standard of yyyy-mm-dd.

 

[Michael.Koppelgaard]

Wow ... thank you for a great explanation! Now I understand