Insert query rearranges date value.

[Jarryd]

Hi,

I have the following append query that inserts a record based on some
variables, two those being dates. It works perfectly apart from one thing:
it swaps the days with the months when inserting the date values. I have
message boxed the values out and they are being read correctly, it only
muddles them up when it inserts them into the DB. However, it doesn't swap
them if it the days are greater than 12. For example, 15/10/2005 inserts
fine, but 12/10/2005 inserts as 10/12/2005:

qrySplit = "INSERT INTO Order_Details ( Orders_Link, Quantity, Weight,
Width, Length, [X-Works_Del_Date], [Del_Date], SAP, IN_Completed ) VALUES("
& ordLink & "," & Quantity & "," & Weight & ", " & Wdth_U & ", " & Len_U &
", #" & XW_U & "#, #" & D_Dt_U & "#, '" & S_U & "', " & In_comp & ");"

Any ideas as to what could be gonig wrong?

TIA,

Jarryd

 

[Douglas J. Steele]

Sounds as though you have your short date format set to dd/mm/yyyy. Access
will not respect that in SQL statements. It'll treat 15/10/2005 as 15 Oct
2005, since there is no 15th month, but regardless of what your short date
format has been set to in Regional Settings, it's going to treate 12/10/2005
as 10 Dec 2005.

Check out Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html
or what I have in my September 2003 Access Answers column for Pinnacle
Publication's "Smart Access" newsletter. (The column and accompanying
database can be downloaded at
http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[Jarryd]

Hi Douglas,

Thanks for such a speedy reply. I must admit that is a terrible thing to
learn about MS. I suppose it is easy enough to get around it, but it really
is rather annoying that they design a product that will coax the rest of the
wolrd to it the USA way. Not very nice. Anyway, thanks for elucidating me
of this little oddity.

Cheers,

Jarryd

Sounds as though you have your short date format set to dd/mm/yyyy. Access
will not respect that in SQL statements. It'll treat 15/10/2005 as 15 Oct
2005, since there is no 15th month, but regardless of what your short date
format has been set to in Regional Settings, it's going to treate
12/10/2005 as 10 Dec 2005.

Check out Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html
or what I have in my September 2003 Access Answers column for Pinnacle
Publication's "Smart Access" newsletter. (The column and accompanying
database can be downloaded at
http://www.accessmvp.com/djsteele/SmartAccess.html)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

I have the following append query that inserts a record based on some
variables, two those being dates. It works perfectly apart from one
thing: it swaps the days with the months when inserting the date values.
I have message boxed the values out and they are being read correctly, it
only muddles them up when it inserts them into the DB. However, it
doesn't swap them if it the days are greater than 12. For example,
15/10/2005 inserts fine, but 12/10/2005 inserts as 10/12/2005:

qrySplit = "INSERT INTO Order_Details ( Orders_Link, Quantity, Weight,
Width, Length, [X-Works_Del_Date], [Del_Date], SAP, IN_Completed )
VALUES(" & ordLink & "," & Quantity & "," & Weight & ", " & Wdth_U & ", "
& Len_U & ", #" & XW_U & "#, #" & D_Dt_U & "#, '" & S_U & "', " & In_comp
& ");"

Any ideas as to what could be gonig wrong?

TIA,

Jarryd

 

[Rick Brandt]

Hi Douglas,

Thanks for such a speedy reply. I must admit that is a terrible thing to
learn about MS. I suppose it is easy enough to get around it, but it really
is rather annoying that they design a product that will coax the rest of the
wolrd to it the USA way. Not very nice. Anyway, thanks for elucidating me of
this little oddity.

If as a developer you create a query with a date literal in it would you really
think it better if your query ran differently depending on a user's Windows
settings? MS had to pick *some* consistent format to apply when an ambiguous
date literal is encountered. It is hardly surprising then that they chose the
format used by the majority of their user-base.

The best thing to do is to never use a date literal that is ambiguous.

 

[Jarryd]

Hi Rick,

I am not so sure that I follow. You enclose a date variable with ##,
correct? So, as a developer I would find it easier to have the system
recognise that as a date and read it in accordance with the users Regional
Settings. That way, when I develop something, I personally don't have to
write code that will check the settings and interpret the date, for every
app I write that might be distributed to users across the world. So yes, I
do think it would be better that my query ran differently depending on a
user's Windows settings. Why wouldn 't I? Am I not understanding
something? According to the web page forwarded to me by Douglas it used to
work that way, but was changed with the new 32-bit platform. I can only
assume their might have been some kind of technical issue, but again, they
could work it out if they wanted to. And I am not so sure that taking the
view that it all comes down what would suit their largest client-base and
the rest will just have to put up with it is a very good business strategy
in an international market. Anyway, this is a technical forum and this
argument is not a new one. I have read loads of complaints about this very
issue in the last couple of hours and it seems clear to me that something
could be done, but just won't.

Cheers,

Jarryd

 

[Douglas J. Steele]

I think Rick's point was that if you put #12/10/2005# as a constant in your
application, it would be a bad thing if it was treated as 10 Dec, 2005 for
Joe, and 12 Oct, 2005 for Mary. It really doesn't matter whether your
application is going to be used around the world or not. You have no control
over what settings your users choose, so you need to ensure that your
application will work regardless what wrinkles they throw you.

Note that while Allen's article mentioned that the treatment of # changed in
the 32-bit world, he also pointed out that "In all versions of Access
(including the 16-bit versions), JET SQL clauses require dates in American
format."

If you want to set a constant date, use the DateSerial function:
DateSerial(2005, 10, 12) to get 12 Oct, 2005, or DateSerial(2005, 12, 10) to
get 10 Dec, 2005.