Problems with regional date formats

[Doug]

I have an Access 2002 .adp application that uses the NOW() function a lot to
update datetime fields in SQL Server. The problem I have just run into is
that some of my users are Canadian and have selected the Regional Language
Preference of English (Canada). On those workstations when the code below is
executed SQL Server generates an error because the date is in the wrong
format:

strsql = "update tbluser set lastcomputername = '" & scomputername & _
"', lastlogin = '" & now() & "' where userid = '" &
Me.username & "'"
cn.Execute strsql

Now() = '21/06/2007 hh:mm:ss' instead of '06/21/2007 hh:mm:ss' and SQL
doesn't like it. I tried to create a datetime variable and set that = now(),
but it is still in the Canadian Date format.

Any suggestions on how to get around this?

 

[Douglas J. Steele]

Regardless of what your Short Date format may have been set to through
Regional Settings, you need to use yyyy-mm-dd format in SQL statements.

Try:

strsql = "update tbluser set lastcomputername = '" & _
scomputername &"', lastlogin = '" & _
Format(now(), "\yyyy\-mm\-dd hh\:nn\:ss") &_
"' where userid = '" & Me.username & "'"

 

[Douglas J. Steele]

BTW, I'm assuming that you're actually running this SQL in a pass-through
query.

If not, and you're running it against a linked table, you need to use #
delimiters, rather than '.