Access Query dates

  • Thread starter Thread starter Gerald
  • Start date Start date
G

Gerald

When running a query that then exports data from 'Access'
into 'WORD', any fields in date format are always changed
back into the American system (m-d-y). This is despite
the entire computer being set up as English (d-m-y) and
it even happens when formatting is in words, so a date
entered as 3-4-04 (meaning 3rd April 2004) appears in the
WORD document as 4th March 2004. This is very risky when
confirming appointments!! Can anyone tell me what to do?
 
IMHO, this is a bug, but I'll try to explain why I think it works this way.
Date/Time values stored in Access are completely locale-independent.
They're really Double numbers - the integer part is the number of days since
30 Dec 1899, and the fractional part is the time in fractions of days
accurate to seconds. SQL in Access always deals with date strings
internally in U.S. format (cheeky Americans) -- so this is probably what is
happening on the export. However, it SHOULD convert the string to the local
format.

Try converting the date in the query to a string in the correct format for
your machine.

RealDate: Format([MyEnglishDate], "dd/mm/yyyy")

The query no longer sees this as a date/time value - Format converts it into
a string (text).

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top