Date problem with CreateQueryDef

[Doppenberg]

Can someone please help me? I am using a dutch date format (dd-mm-jjjj).
When a user types a date into a form and pushes the report button. A report is created based on a newly created Query.

In the CreateQueryDef I use the name of the controlfield, surrounded by #. This works ok.

The problem is that in the new query the criteria is the date from the controlfield with the days and months swapped (mm-dd-jjjj)
How can I keep my dutch date format ?
The controlfield's format is set on short date notation in dutch, so that should be ok.

Any help will be greatly appreciated.

Tim.

 

[Pat Hartman]

Access keeps all dates stored internally as serial numbers. The origin date is Dec 30, 1899 which is stored as 0. Dec 31, 1899 is stored as 1, Jan 1, 1900 is 2. Dates prior to the origin date are stored as negative numbers. Dec 29, 1899 is -1, etc. Time is stored as the decimal portion of the number. How a date is formatted is determined either by the windows default settings or a specific format for a control. This does not alter how the date is actually stored. That said, we can get on to your problem. SQL (not just the Access varient) supports only US standard date formats. Therefore, if you need to use a variable date, it needs to be in US format - mm/dd/yyyy. You can convert your Dutch format date as you build the query.

 

[Doppenberg]

Thanks Pat,

I found out that a funcion called "Datevalue" takes care of the problem.
When you use this function instead of
#date# , the date is automatically converted to the US format.

Gr. Tim.