Access 2003 Date Problem

[hegartyjp]

Hi All,

I have created a database in access 2003 which is used to store meber details in a club.

When a new member is added, the joining date is set to the current date and inserted in the table using an Insert SQL string in the form.

This works on my PC in work and at home but in the club the dates become americanised for the first 12 days of each month. i.e. a new member on 10-Jan-06 is inserted in the table as joining on 01-Nov-06.

If I use a message box to display the date being read in from the form, it is in correct format so it is only the actual inserting in the table where it gets converted.

I have set the regional settings on the PC to UK and still no good....

Please Please tell me why this is happening.....

Any help greatly appreciated

 

[RoyVidar]

When doing dynamic SQL, you need to feed an unambiguous date string to the databae engine. Use for instance

[tt]...set mydate = #" & format$(me!txtDate, "yyyy-mm-dd") & "#...[/tt]

See for instance Return Dates in US #mm/dd/yyyy# format form more info.

Roy-Vidar

 

[hegartyjp]

Thanks RoyVidar,

I will give this a try.

I just can't understand why it works ok on 2 pcs and not on another.

How come I dont need to put this code in on all versions

 

[JoeAtWork]

The PC where it doesn't work probably has different regional settings.