US Date Format Problem

[saross]

Hi all, I've searched the forums on this and although I've found several threads with similar issues, none of the suggestions have worked for me. I don't know if it's because I'm using Access 2003???

Anyway, I'm running a search option for users that allows them to enter a date (English format dd/mm/yy) and compares it against records in a table and returns matches. My problem (as for others I've come across here) is that it assumes in the query that the date is in the US format mm/dd/yy so doesn't return matches.

My regional settings are on English.

When I searched the Help section on VBA, it said:

Use International Date Formats in SQL Statements
You must use English (United States) date formats in SQL statements in Visual Basic. However, you can use international date formats in the query design grid.

Is there a way round this?

Thanks in advance...

 

[Shredderr]

Im not really sure but, lets though you have field "Date/tim" in your table. Click and set that field format "dd;mm;yyyy" not sure if that helps, but i tryed

 

[ColinEssex]

Dates are not stored in Access as "dates", they are stored as a number (of days since 01/01/1901)

Whichever format you have therefore, on your PC, you should retrieve data - assuming there is some for that date.

When hardcoding a date in VBA however, you do need to use the USA format. (#01/16/2001#)

Col

 

[saross]

When hardcoding a date in VBA however, you do need to use the USA format. (#01/16/2001#)

Thanks for this. Do you think then that rather than use VBA, I should create a query that uses the input box of the form as it's search criteria instead? Would that resolve it?

 

[Rich]

Using a query with form input boxes certainly makes date comparisons easier

 

[iangirven]

dates

Saros,

For what its worth I had this problem which resulted in errors validating entered dates.

I found this wasnt a problem with access or the coding i used, as it didnt happen with all computers the db was on. It turned out to be the regional settings of the individual computers.

Once the user had changed their regional setting to english(uk) the problem was resolved.

Not sure if this will sort your problem tho but may be worth a look.

regards
ian