Formatting Date within Do.Cmd.RunSQL

[iankerry]

Hi All

I have a routine which keep putting in a date in the U.S. format and I am in the UK. I have tried to research and come up with a solution but still can't get it to work. Does anyone have any clues please?

Application.DoCmd.RunSQL "INSERT INTO temp_tbl_QBData([flicksfeedbackleft], [class], [qNAME], [EventID], [vatamount], [totalvat], [totalinv], [acct], [incomeAccount], [filmNameDescription], [today], ) VALUES

...lots of other fields here....


"#" & Format(.Fields("today").Value, "dd-mm-yyyy") & "#,"

Thanks for any help.

 

[pbaldy]

http://allenbrowne.com/ser-36.html#Format

 

[iankerry]

Thanks Paul. I couldn't really see anything on there that helped. I tried the format \ / between the dates but it didn't make any difference.

At least I can't see anything, especially given the syntax I am using.

Can you see something obvious that is wrong with my statement?

Thanks

 

[pbaldy]

You're not using US format here:

"#" & Format(.Fields("today").Value, "dd-mm-yyyy") & "#,"

as the link says, you must.

 

[iankerry]

Ah, I see. I have to force it to go into US mode, then some magic works when I run it - it turns it to UK mode.

That kinda felt counter-intuitive, but it works.

My line now looks like this:

"#" & Format(.Fields("today").Value, "mm\/dd\/yyyy") & "#,"

Thanks very much, and have a great weekend.

Ian

 

[pbaldy]

No problem Ian. Internally, dates are just stored as a number, the format is for us. In code you have to use US format so it stores the correct number.