Date() does not return the date correctly

[Dave Reardon]

A strange one! I distribute a database front end to a number of computers. On
the vast majority, the date() function returns the correct current UK date.
However, on a couple, it thinks we are in March today, as it 'sees' the date
in American format, so 03/12/2008 is March 12. I have checked the clock,
regional settings and all are correct. I have also looked in Access Options
under Language Settings and ensured it is set to English (UK). Am I missing
something?

Dave

 

[Douglas J. Steele]

Check what Allen Browne has at http://www.allenbrowne.com/ser-36.html or
what I had in my September, 2003 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Dave Reardon]

Thanks Doug,

I understand about formatting dates etc, and the different formats for
different regional settings. That wasn't my question. The point is that the
workstations in question are looking at a SQL server back end, which gives
them the same date as every other workstation on the network. I have checked
regional settings and even the date format within regional settings on the
offending machines, etc yet they still interpret a system variable of Date()
as American format.

Dave

 

[Douglas J. Steele]

You never mentioned SQL Server was involved. SQL Server does let you set the
date format (see SET DATEFORMAT in BOL)

However, even if you do that, Access does have the capability to screw up
the interpretation of the date, because there are parts of Access that do
not respect the user's Short Date format as set in Regional Settings in the
Control Panel. In an Access query, nn/nn/nnnn will ALWAYS be treated as
mm/dd/yyyy, unless it's after the 12th (in other words, 03/12/2008 will
ALWAYS be treated as 12 Mar, 2008, but 13/12/2008 will be correctly
interpretted as 13 Dec, 2008 since there isn't a 13th month)

Afraid, though, that I have no answer why the same database would perform
differently on different workstations if the regional settings are the same.