Date problem - day/month swaps

[Trancemission]

I have a really simple problem which is causing headaches and I don't understand where I have gone wrong.

I have a form with a field called thedate. This is a free form unformatted text box.

I the have a query executed when I click a button [am using DAO] which selects records from a table depending on the date enter.

My table contains the 2 fields startdate and enddate. My query selects from the table where startdate <=# & me.thedate & # and enddate >=# & me.thedate & #.

The problem is I want to enter the date in format DD/MM/YYYY yet in the query it seems to be evauluating my value like MM/DD/YYYY.

I am really not sure where I have gone wrong and I am sure there is a basic setting in access to set format.

Cheers



Trancemission
=============
If it's logical, it'll work!

 

[lupins46]

If you are building the sql string in code then you must prevent SQL from interpreting the date as mm/dd/yy by giving it a format which is unambiguous.

One easy way to do this is to use:

where startdate <=# & format(me.thedate, "yyyy-mm-dd") & # and enddate >=# & format(me.thedate,"yyyy-mm-dd") & #.

However it seems a bad idea to ask a user to input a date into a free text box and expect them to use a specific input style. At the very least you should set the format property for your textbox to medium date so that you get visual confirmation that the date is what you thought you entered.

 

[Trancemission]

Thanks for that will include in my query.

Will also edit the text box to make sure it is correct.

Thanks

Trancemission
=============
If it's logical, it'll work!