MySQL date problem

[Dave_cha]

Hi folks,

I built a database earlier this year which involved users importing date based data. The destination data tables are linked tables connecting to a MySql database.

To avoid duplicate data, a DCount is performed on the destination table's date field for dates equal to a given date (normally the current date). It the count > 0 then a warning is given. The 'given' date is selected by the user and is displayed in a txt box with a long date format. The form script refers to this field when running the DCount .

All was fine until last Friday (1st April) when the DCount for records with a date of 01/04/05, counted data entered on the 04/01/2005. It's obviously reversing day and month but I'm not sure why or how to fix it. Also, new data appended to the destination table reversed the date field from 1st April to the 4th Jan.

Any idea?

Thanks,
Dave

 

[Mile-O]

Access, unfortunately, loves the American date format.

When using dates use the Format function to explicitly declare --

i.e.

"[MyField] = #" & Format([MyDate], "mm/dd/yyyy") & "#"


As part of:

=DCount("[MyField]", "[MyTable]", "[MyField] = #" & Format([MyDate], "mm/dd/yyyy") & "#")

 

[Dave_cha]

Thanks SJ, reformatting the date did the trick.
Really appreciated the help.

Rgd's,

Dave