Date problems in queries

[eddy]

I have a query that pulls out a date. It's value is 05/10/2007 as DD/MM/YYYY

Then I do a basic sql statement via a recordest with this code

UPDATE TempValues SET LastShippingDate = #05/10/2007#

The problem is the way it gets stored in the table. I see it as 10/05/2007 DD/MM/YYYY.

The format field for that field is short date DD/MM/YYYY. Regional setting for this computer is DD/MM/YYYY.

Totally at a loss on this one

 

[KenHigg]

Not quite sure whatthe issue is?

fyi: Dates are actually stored as an integer portion of a variant so the display format shouldn't matter...


???
ken

 

[boblarson]

I have a query that pulls out a date. It's value is 05/10/2007 as DD/MM/YYYY

Then I do a basic sql statement via a recordest with this code

UPDATE TempValues SET LastShippingDate = #05/10/2007#

The problem is the way it gets stored in the table. I see it as 10/05/2007 DD/MM/YYYY.

The format field for that field is short date DD/MM/YYYY. Regional setting for this computer is DD/MM/YYYY.

Totally at a loss on this one

Use the U.S. format for dates when using the query. It's a Microsoft thing.

 

[eddy]

Use the U.S. format for dates when using the query. It's a Microsoft thing.

What do you mean by that? Should I be formatting it as MM/DD/YYYY before I write to the table??

 

[boblarson]

UPDATE TempValues SET LastShippingDate = #10/05/2007#

At least that's what I believe needs to happen, from posts that I've seen around this type of stuff. If I remember correctly, when using SQL you need to use the U.S. formatting.