inconsistent date format in unbound text box 1

[purpledawn]

I am creating a form for inputs for a query to create a report. 2 of the boxes are unbound and will have dates entered in them, specifying a range. The users are Spanish and so will enter dates in dd/mm/yyyy format. I have changed the Windows regional settings to one where this date format is the default. Dates in tables and data entry forms are all displayed using dd/mm/yyyy format.

The problem is this:

When I type in a date where the day is 12 or less, Access uses mm/dd/yyyy to query. ie if I enter 1/3/2001, it querys using Jan. 3., rather than Mar. 3. However, if the day is 13 or more, it uses dd/mm/yyyy format, i.e. 15/3/2001 querys using Mar. 15. The date format in these boxes is the default for Short Date, and in the example it is in dd/mm/yyyy format, as it should be according to the regional setting. The input mask is 00/00/0000;0;_

I want it to query using Mar 3 when 01/03/2001 is entered.

Does anyone know how to make Access cooperate in this matter?

Thanks.

 

[DJS2]

Regardless of the input date format,I have found that Access will always query using American dates. You will need to create a procedure to convert this date to American for the query.

Day1 = Your Date Field

Day2 = Month(Day1) & "/" & Day(Day1) & "/" & Year(Day1)

You can then use this in your query as one of the parameters.

 

[purpledawn]

DJS2 - Thanks for your reply. I've found that if the day is greater than 12, then Access will query using the Latin American date format, if the Regional Setting is for a Spanish Latin American country. ie inputing 1/1/2001 and 17/3/2001 will query between Jan 1 and Mar 17, without having to reformat the date in the program as in your example.

I hope I don't seem ungrateful for your help, but this solution still doesn't fix the problem of being able to input a date in the right format. As it is now, my user cannot enter a date such as March 3 in the format they would expect to, regardless of regional settings and date formats.

 

[hymn]

Being from the UK what you are after is standard.
Try resetting your the Windows regional settings to the Uk

Hope this helps
Hymn

 

[purpledawn]

Thank-you DJS2, turns out you were right after all. As I see it, it is SQL in Access that has problems with date formats; SQL statements need dates in U.S. format. Dates were being entered OK, just SQL was interpreting inconsistently. Thanks again for your help.