Re: Help with query on a Date field for UK date style dd/mm/yyyy ???

From: John Vinson (jvinson_at_STOP_SPAM.WysardOfInfo.com)
Date: 03/13/04 

Date: Fri, 12 Mar 2004 18:03:19 -0700

On Fri, 12 Mar 2004 15:50:10 -0500, "Bill" <softwaredemo123@yahoo.com>
wrote:

>Hello,
>
>I have developed an app that has been working fine for my US users.
>
>Unfortunately I had hard coded and set some date data for US standard
>mm/dd/yyyy.
>I have fixed all references to date so that it now uses the short date
>format from the region settings in windows.

This affects how the date is DISPLAYED. It has no effect on how it's
STORED.

>But, I am still having one issue.
>
>On one form I generate SQL statements from user supplied data in some
>unbound txtboxes.
>When it comes to the date field the SQL generated looks like:
>
>SELECT * FROM MainTable WHERE SaveDate = #12/03/2004#
>
>Remember I now have all my dates stored as UK dd/mm/yyyy.

Well... no. You don't. The dates are stored, as all date/time fields
are stored - as a Double Float number, a count of days and fractions
of a day since midnight, December 30, 1899. Date literals in Queries
or in VBA are *always* interpreted in USA month/day/year format,
regardless of the computer's date/time setting. Either use mm/dd/yyyy
or an unambiguous format such as 3-Dec-2004 or the ISO 2004-12-03.

>When I open the table and look at the dates, it all looks correct and in
>this format.
>
>There are 2 records with a SaveDate of 12/03/2004 - which means
>march 12, 2004
>
>When I run the above query it says no records found.
>If I change the query to read : SELECT * FROM MainTable WHERE SaveDate =
>#03/12/2004#
>
>It returns the records from the table with the date fields showing
>12/03/2004.
>
>Why is it assuming my SQL query is mm/dd/yyy instead of dd/mm/yyyy
>????

Because the programmers who wrote Access were Americans, I guess...
<G>

>On other thing. If I use the " Like" predicate with the * wild card as
>in this Query:
>SELECT * FROM MainTable WHERE SaveDate Like '12/03*'
>It finds the 2 rows with the date 12/03/2004

Dates are NOT text strings. This is converting the Date/Time value to
a string and then searching that string.

                  John W. Vinson[MVP]
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Relevant Pages

  • RE: Payroll Query
    ... I have tested both functions and the query against some dummy data, ... Yes the text column is in the format hh:mm and no there are no null values, ... function to return it as a Date/Time data type provided that that no value ... Public Function TimeSumAs String ...
    (microsoft.public.access.queries)
  • 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 format/sort problem
    ... There is no such thing as a Date/Time data type which only contains ... The Format propety of the field determines how the data in the field is ... In my first query I need to format it: ... This works fine and the query sorts in chronological order however when I ...
    (microsoft.public.access.queries)
  • Re: DCOUNT format question
    ... > When I enter your string into the control on the form, ... >> Yes, your format is incorrect. ... >>>I can get this to work in a totals query but not on a form. ... >>> initials ...
    (microsoft.public.access.forms)
  • RE: Payroll Query
    ... I missed the from clause from the query: ... Yes the text column is in the format hh:mm and no there are no null values, ... function to return it as a Date/Time data type provided that that no value ... Public Function TimeSumAs String ...
    (microsoft.public.access.queries)