Dates in forms (1 Viewer)

[garywood84]

I have a form to set parameters for queries. Two boxes on this form allow the entry of dates - a start date and and end date. This is turned into a Between X and Y statement and fed into the query so that records are shown between the dates specified.

For some reason, when dates like 01/09/2006 are entered, these are interpreted as 9 January 2006, not 1 September 2006. My system settings have the correct date format.

Can anyone advise how I can get around this?

Thanks,

Gary

 

[macca the hacke]

It is a quirk of access that you must use US date formats in SQL. You will therefore have to change the format of the field you are querying so that it is in this format, before applying your start/end date criteria to it.

 

[garywood84]

Many thanks for that. Is there any way of building into my VBA code some script which would convert the user's British date input into the US format?

 

[macca the hacke]

You would just need to format the date to format([datefield],"mm/dd/yyyy"). You will need to change [datefield] to the name of the field(s) which contain the start and end dates (you will have to format each date seperately)