Re: Access Query dates

From: John Viescas (JohnV_at_nomail.please)
Date: 03/31/04 

Date: Wed, 31 Mar 2004 07:17:21 -0600

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)
"Gerald" <enquiry@drhales.co.uk> wrote in message
news:1625e01c41717$6b329420$a301280a@phx.gbl...
> 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?

Relevant Pages

  • Re: Having trouble with calculating time differences in ms access
    ... Dim intMinutes As Integer, intHours As Integer ... an hh:mm format with a function such as: ... Public Function HoursFormattedAs String ... This is because the date/time ...
    (microsoft.public.access.gettingstarted)
  • Re: Time difference
    ... As John S has pointed out, a date/time data type is entirely inappropriate ... string, using the Format function, before you do any manipulation. ... Dim lSeconds As Long ...
    (microsoft.public.access.queries)
  • Re: Date Criteria Question
    ... field name) that was type Date/Time. ... If it is not a Date/Time type, ... "hierarchy" becomes a string sort, ... I have the tables and forms set to short date format which acepts dd mm yy ...
    (microsoft.public.access.queries)
  • Re: Date Criteria Question
    ... Yes the date type is date/time and yes I had named the field Date (which I ... "hierarchy" becomes a string sort, ... I have the tables and forms set to short date format which acepts dd mm yy ... tblTrainingSessions As T ...
    (microsoft.public.access.queries)
  • Re: Help with query on a Date field for UK date style dd/mm/yyyy ???
    ... >format from the region settings in windows. ... regardless of the computer's date/time setting. ... >When I run the above query it says no records found. ... a string and then searching that string. ...
    (microsoft.public.access.queries)
Loading