Is this nonsense or have I missed something ?

[Ochil]

I'm new to Ms Access (I'm a unix DBA for 20+ years) and have been developing a small app for a friend. Now i know that in Access you must use the US date format in queries, but I came accross this when my app was causing me grief ;

Table : timesheet
Columns : tsName,tsCode,tsDate,tsHours

query1 : UPDATE timesheet SET tsCode = "P" , tsHours = 6 WHERE tsDate = #25/10/2010#

result : 2 records (25th Oct. 2010) updated

This is what i expected , now the 2nd query ,

query2 : UPDATE timesheet SET tsCode = "P" , tsHours = 6 WHERE tsDate = #10/25/2010#

result : 2 records (25th Oct. 2010) updated

how can both queries translate the date to the 25th Oct. 2010 ?

Surely one of them is invalid ?

 

[JANR]

Well since there isen't any mounth number 25, access guesses correctly.

Try with 2/1/2010 and access won't get it right.

This is either 2. Januar or 1. februar

JR

 

[Ochil]

Thats my issue , Access 'guesses' , suppose the month should have been 12 and not 25 but somehow got corrupted by the code , instead of failing it 'guesses' and updates the wrong rows and now your data is corrupted and the bug that corrupted your code has gone unnoticed.

 

[JANR]

Correct, rule number 1

NEVER let Access guess your intent always SpellItOut.

JR

 

[Dairy Farmer]

Because Access needs #USdate# and most of the world doesn't use the US date format, I always try to use dates as [Date1] to input dates into queries and other formulars. Then Access takes the date format that is used by the computer regional settings and converts it to US format.

Don't know how to explain it better, but those of you who write code will understand.

UPDATE timesheet SET tsCode = "P" , tsHours = 6 WHERE tsDate = [Date1]

 

[Brianwarnock]

Because Access needs #USdate# and most of the world doesn't use the US date format, I always try to use dates as [Date1] to input dates into queries and other formulars. Then Access takes the date format that is used by the computer regional settings and converts it to US format.

Don't know how to explain it better, but those of you who write code will understand.

UPDATE timesheet SET tsCode = "P" , tsHours = 6 WHERE tsDate = [Date1]

That's one of the reasons I use a Form to drive my queries, entering the date into a date formatted field would cause 10/25/2010 to be rejected , i'm in the UK incase you haven't noticed.

Brian

 

[boblarson]

Perhaps you should read this.

 

[ChipperT]

Thats my issue , Access 'guesses' , suppose the month should have been 12 and not 25 but somehow got corrupted by the code , instead of failing it 'guesses' and updates the wrong rows and now your data is corrupted and the bug that corrupted your code has gone unnoticed.

That is why I always:

1. include an example of exactly what I want in a field in a form, when formatting matters, such as "Date format should be 'mm/dd/yyy"

2. Validate ALL data in a module BEFORE inserting in or updating the database.

#2 is important for a number of reasons other than formatting.