Access/Jet SQL: Date/time issues

[robtyketto]

Greetings,

I'm using Flash MX connecting to an MS Access database via JET.

I've stored dates using Flash/Jet into my database as 'dd/mm/yyyy hh:mm:ss' into a DATE/TIME field.

However I can't update because of the WHERE clause as I am not specifying the date correctly. I believe syntax wise I need to use # and specify it in american format mm/dd/yyyy hh:mm:ss.

I have the code below working :-

mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = 10 WHERE UserName = 'Rob' AND quizDate > #02/19/2008#"); 

When I add the time it complains and using the "=" clause it complains :-

mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = 10 WHERE UserName = 'Rob' AND quizDate = #02/19/2008 12:03:32# "); 

Can anyone please assist, ive spent hours messing around with date/time formats, very confused.

Thanks
Rob W

[robtyketto]

It appears ok with UK dates it doesnt like the time format for some reason.

mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = 10 WHERE UserName = 'Rob' AND quizDate = #19/02/2008# "); 

That code above runs without errors, when introducing time hh:mm: or hh:mm:ss it all goes wrong :-(

[FishVal]

It appears ok with UK dates it doesnt like the time format for some reason.

mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = 10 WHERE UserName = 'Rob' AND quizDate = #19/02/2008# "); 

That code above runs without errors, when introducing time hh:mm: or hh:mm:ss it all goes wrong :-(

Hi, Rob.

First of all you should never use date format other than US in SQL. The example you've provided works with UK format only because Access could not interpret it as valid US format date and makes necessary corrections.

The second thing - could you kindly explain what you mean by " ... it complains ..." in post #1.

The third - try to use single quote delimiters around date constants instead of #.

Regards,
Fish

[robtyketto]

Thanks for the reply.

I amended my actionscript code and now the date is stored as "mm/dd/yyyy hh:mm:ss" which populates the DATE/TIME field (QuizDate) in the access database.

TextDate is a string in actionscript which is used to successfully insert the QuizDate into the table as "mm/dd/yyyy hh:mm:ss"

When I want to update the table based on field Username and Date it goes horribly wrong:-

The code below:-

mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = " + UserScore + " WHERE UserName = '"+ UserName +"' AND QuizDate =  '"+textDate+"' "); 

Which puts SINGLE QUOTES around the Username and TextDate errors with message

Data type mismatch in criteria expression SQL statement

This code:-

mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = " + UserScore + " WHERE UserName = '"+ UserName +"' AND QuizDate =  #"+textDate+"# "); 

Which puts the user name in SINGLE QUOTES and the Date as #mm/dd/yyyy hh:mm:ss#

Which errors with :-

Parameter object is improperly defined. Inconsitent or incomplete information was provided SQL statement.

[FishVal]

Have you tried to run the same in Access query editor?

[robtyketto]

Thanks for the speedy reply.

When I was storing the dates in UK format in the database I could run the update query in Access with UserName and Date parameters without ANY problems with SINGLE QUOTES or #.

Since I changed the textDate within Actionscript and printed it out to american format "mm/dd/yyyy hh:mm:ss" I assumed it would be stored in the database in this way as I simply insert this String as is to a DATE/TIME field.

I've just checked teh database and its storing it in UK format ???
Suprised me, so the SQL qill be attempting to compare the UK format (stored in database) against textDate field (USA format).

Im looking now at why access would store it in UK format?

[FishVal]

To the best of my knowledge date format is only the way to represent date in Access table/query view unless the table field is of Text type. On the other hand SQL ANSI standard determines date constants to be used in US format.
These are separate things, they are not supposed to interfere.

[robtyketto]

Heres where I am right now with the problem and hopefully a clearer explanation of the current problem:-

I've written an application which stores the users name and current date.

In flash I've formatted a STRING with the date format of "mm/dd/yyyy hh:mm:ss" i.e. American Format

This STRING is then stored into the database without problems in a DATE/TIME field as "dd/mm/yyyy hh:mm:ss' as I am a UK user.

When I wish to update the table based on the value of User name and date the problems occur.

mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = " + UserScore + " WHERE UserName = '"+ UserName +"' AND format(QuizDate,(\"mm/dd/yyyy hh:nn:ss\")) =  #"+textDate+"# ");

Errors with

SQL query has failed for the following reason: Parameter object is improperly defined. Inconsistent or incomplete information was provided SQL statement: UPDATE OverallResults SET Results = 30 WHERE
UserName = 'sf' AND format(QuizDate ,(#mm/dd/yyy hh:nn:ss#) = #02/20/2008 15:45:38#

Testing shows if I remove the TIME part hh:mm:ss it runs without errors.

When testing SQL within Access its self all is ok no problems and happy if i use SINGLE QUOTES or HASH for date value, Ive been struggling with this for days.

Any help would greatly be appreciated.

Thanks
Rob W

[FishVal]

Ok, Rob.

I'm somewhat frustrated with it.
Your first syntax was just fine as for Jet SQL.

mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = 10 WHERE UserName = 'Rob' AND quizDate = #02/19/2008 12:03:32# "); 

I will ask our community to pay attention to your question.

Regards,
Fish.

[MMcCarthy]

Rob

One quick question. When storing the date in the database what is the data type of the field and assuming it's date/time what date format is defined?