Date Format issue switching Region

[Joakim N]

Hi,

I created an access database 5 years ago i Sweden. Now, a person in Finland wants to run the program and that created error. I switched Region in control panel on my computer to Finland from Sweden. Now I get the same error.

In Finland they give Date in format "d.m.yyyy'. In tables inside my access database, Dates are written like '17.11.2023'. If I write my WHERE clause in SQL code using #17.11.2023# creates an error. However, using #17/11/2023# is working. There seems to be a mix of date format using Region = Finland.



Has anyone any idea what's happening?

I just made a test by using function CDate using Region = Finland:

CDate("17.11.2023") = 17.11.2023
CDate("17/11/2023") = 17.11.2023

[Minty]

Try using a different format #yyyy-mm-dd#

2023-11-17

As far as I am aware this is unambiguous and works everywhere.

[Joakim N]

We are using "yyyy-mm-dd" is Sweden. Probably why it has been working without any issue because it's only Swedish people who has used the program.

However, it's just so boring to tell the user in Finland to use "yyyy-mm-dd" just to run this application, when they use "d.m.yyyy" everywhere else.

[Minty]

You don't (shouldn't) need to get the user to adjust, on forms it should accept the windows locale defaults inputs
It's only if you write a SQL statement in VBA it will (could) have an issue.

[Joakim N]

OK. I use a lot of SQL Statement in VBA code in this application.

Thanks for your help.

[Minty]

I'm probably teaching grandma to suck eggs but use a function to do the hard work: Simply pass in the form value SQLDate(Me.MyDateControl)
I have an similar function for passing dates to SQL Server so adds the single quotes required in the server.

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    'Adjusted Minty 2013 - to a more universal format
    
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#yyyy-mm-dd\#")
        Else
            SQLDate = Format$(varDate, "\#yyyy-mm-dd hh\:nn\:ss\#")
        End If
    End If
End Function

in the immediate window;
?sqldate (date())
#2023-04-02#

[Joakim N]

That is good tip.

Thanks!

[Minty]

There is an side benefit to using this - when you are debugging, the date in this format is unambiguous to the eye as well.
No wondering if it mm/dd/yyyy or dd/mm/yyyy or if it's converted or not. Makes things much more obvious.

[Joakim N]

I replaced

dteMy

with

Format(dteMy, ”YYYY-MM-DD”)

after WHERE in my SQL in VBA code. Now it’s working.

Thanks again.

[Welshgasman]

You still need the # though, surely. ?
Plus be aware Format() converts it to a string?