SQL Date Nightmare connecting via JET

[robtyketto]

Greetings,

I'm using flash mx and MDM zinc which i assume connects using the JET rather than ADO database engine.

For the life of me cannot get a simple update to work due to the dates
involved, lost count how many hours have gone by trying!!!

Within access I can easily switch quotes and hash symbols and all SQL THREE SQL queries work without problems on my database i.e.

UPDATE UserOverallResults
SET Results = 20
WHERE UserName ='Robbie'
AND Quizdate= '19/2/2008 12:15'

UPDATE UserOverallResults
SET Results = 20
WHERE UserName ='Robbie'
AND Quizdate= "19/2/2008 12:15"

UPDATE UserOverallResults
SET Results = 20
WHERE UserName ='Robbie'
AND Quizdate= #19/2/2008 12:15#

Within the database the fields are defined as :-
Results (Integer) Username (Text) and QuizDate (Date/Time) within MS ACCESS DB schema.

in Flash MX TextDate param is a STRING and All parmaters are populated appropriately (see this later in error message).

Example of existing date data stored in database in format

"19/02/2008
16:48:11"

as a DATE/TIME field.

My code within in flash is :-

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

I've tried with and without Hashes still no joy ..

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

The error message I get from the code above is ..

SQL Query has failed for the following reason: Data type mismatch in
criteria expression SQL statement: UPDATE UserResultsOverall SET Results =
30 WHERE UserName = 'd' AND QuizDate = Format('19/2/2008 19:4:35'
,"#yyyy\-mm\-dd hh\nn\ss#")

My text string removes the leading zeros not sure if that would cause an
issue or not (its doesnt in MS ACCESS running query) ...

Anyone have any ideas??? Losing my mind here !!

PS I had problems using '&' instead of '+' to concatenate fields.

Cheers
Rob

 

[Uncle Gizmo]

My first guess would be that there's a problem with the English format date, especially if you are passing "19/2/2008" as this date is OK in UK format but no good in USA format. So I would suggest passing a USA format date THUS :: "02/19/2008" and see if that makes a difference.

 

[robtyketto]

Thanks I was just looking into that !!

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

Works!!!

But complains about ...

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

Confused

 

[Uncle Gizmo]

Sorry I Can't be of much help now, I have avoided messing around with the "time" part of dates!

 

[gemma-the-husky]

try formatting the date like, this
QuizDate = Format(textDate,"general date")

you can do

Format(textDate,"long date"), which gives you "12th March 2007"

or

Format(textDate,"long time"), which gives you "12:04:30PM"

but there isnt a long date and long time together

the closest is general date which gives

"12/3/2007 12:04:30PM"

unless this gives you a problem with UK/US dates, you may be OK

 

[robtyketto]

I tried the code below and still get error:

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

Parameter object is improperly defined. Inconsistent or incomplete information was provided SQL Statement.

I can't believe it has no problems with the INSERT where textdate is a STRING in format "dd/mm/yyyy hh:mm:ss" and that happily populates the DATE/TIME field within the MS DATABASE but on comparison it throws a wobbly and ones the date in a very specific format

 

[robtyketto]

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

This works too, so it doesnt like the time section ... hmmmmm

 

[gemma-the-husky]

you are adding spurious characters to the format section - slashes/backslashes- since you are using the format commnad that needs to be syntaxed slightly differently

you need to put it this way

dim strsql as string

strsql = "UPDATE UserOverallResults SET Results = 10 WHERE UserName = 'Rob' AND quizDate = " & format(#02/19/2008 12:03:32#,"general date")

{now have a look at it}
msgbox(strsql)

and then
mdm.Database.MSAccess.runQuery strsql

but it might be

docmd.runsql strsql

i've not seen your syntax before

 

[robtyketto]

Thanks for the reply, the spurious bit n bobs are due to the fact Im using Flash mx (not really the best language to use in conjuction with access!!)

The extra back slashes are to print special characters like ".

IM tempted to jsut save the date from the beginning in US date format.
Then worry about how to convert it after.

All very confusing for a simple date query .. headaches ahead!

 

[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.

 

[robtyketto]

Changing dates within flash to be American mm/dd/yyy hh:mm:ss and having this stored in Access as UK date dd/mm/yyyy *due to windows locale settings.

This SQL runs in MS access ....

UPDATE UserOverallResults 
SET Results = 30 
WHERE UserName = 'Rov' 
AND format(QuizDate,("mm/dd/yyyy hh:nn:ss")) = #02/20/2008 15:46:38#

which Runs but UPDATES 0 Rows.

UPDATE UserOverallResults 
SET Results = 30 
WHERE UserName = 'Rov' 
AND QuizDate = #02/20/2008 15:46:38#

This SQL runs in MS access and UPDATES 1 ROW.

Within Flash the first SQL statement replicated

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

Which replicates the first SQL statement 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 = 'Rov' AND format(QuizDate,("mm/dd/yyy hh:nn:ss") = #02/20/2008 15:46:38#

The below code replicates the second SQL statement and errors with

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

SQL query has failed for the follwoing reason: Parameter object is improperly defiend. Inconsistent or
incomplete information was provided SQL statement: UPDATE OverallResults SET Results = 30 WHERE
UserName = 'Rov' AND QuizDate = #02/20/2008 15:46:38#

Im so confused.

Rob

 

[gemma-the-husky]

its the sql statement - the statemnt HAS to read

UPDATE OverallResults SET Results = 30 WHERE
UserName = 'Rov' AND QuizDate = #02/20/2008 15:46:38#

although it seems that access objects to the way the date AND time is presnted together - so this is the issue to fix

so if you are using extraneous commnds like format you need to set it out differently

strsql = UPDATE OverallResults SET Results = 30 WHERE
UserName = 'Rov' AND quizdate = #" & format(datevariable), "general date" & "#"

to generate the string correctly. its just that you dont seem to have the right syntax for formatting a date expression where time is invloved, and we are struggling to establish what that should be.