SQL Date formats within VBA

[wildsyp]

Hi,

I hope somebody can help me.

I have a very simple bit of SQL code I use to append data to a table from within VBA.

Everything works fine apart from the date which automatically reverts to a US format if the current date is within the first 12 days of a month (ie 05/01/2009 will change to 01/05/2009)

Is there anyway to force the SQL to use a UK format as this is causing havoc with some of my tables. The SQL I use is below.

Cheers
Paul

DoCmd.RunSQL "INSERT INTO Tbl_RevisedEndDateChange ( [Updated By], Change, [Date Changed], [Reason for change] ) " & _
"Select '" & UpdatedBy & "', '" & Change & "', #" & date1 & "#, '" & changereason & "';"

 

[MarkK]

I'm sure there are other ways, but you could Format() the date variable to "Medium Date" which will remove any ambiguity.

... & ", #" & Format(date1, "Medium Date") & "#, " & ...

 

[pbaldy]

This may help:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210069