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

[Bill]

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.

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.
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
????

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



Is this an Access thing or SQL thing?

please help I'm dying here.



Bill

 

[John Vinson]

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...

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.