SQL Date Headaches

[Rob W]

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 they all THREE
work without problems 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#

Results (Integer) Username (Text) and QuizDate (Date/Time) within MS ACCESS
DB schema.
TextDate param is a STRING.
All parmaters are populated appropriately.

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 Resultes =
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

 

[Rob W]

Iv'e hardcoded the values in and it still complains about the date

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

I expected the Jet Date to expect a date in format mm/dd/yyyy hh:mm:ss

Any ideas???

Cheers
Rob

 

[Sylvain Lafontaine]

You could try inverting the month and the day: #02/19/2008 15:00:36#. You
could also try without a time value or with another separator (/) or even
without any separator at all (something like 20080219).

 

[John W. Vinson]

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 they all THREE
work without problems 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#

Results (Integer) Username (Text) and QuizDate (Date/Time) within MS ACCESS
DB schema.
TextDate param is a STRING.
All parmaters are populated appropriately.

Example of existing date data stored in database in format "19/02/2008
16:48:11" as a DATE/TIME field.

Literal dates must be in American mm/dd/yy (or yyyy) format, or an unambiguous
format such as yyyy-mmm-dd. Day/month/year is NOT recognized.

Note also that a query with a time portion must match exactly, to a few
microseconds, since date/times are stored as Double Float counts of days. This
is OK if the query criterion comes from the same table, but if either the
stored date or the criterion is calculated, it may be off by less than a
second - and since you can't edit dates to less than a second, it's tough!

 

[Rob W]

Can I not just attempt to format it as below on a comparison rather than
change the format I store the date in?

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

 

[Rob W]

Further testing shows this is ok

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

So it does'nt like the time element ... Interesting ..

 

[John W. Vinson]

Further testing shows this is ok

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

So it does'nt like the time element ... Interesting ..

Well, it DOES like it; it's just that - apparently - what's stored in the
table is actually #02/19/2008 00:00:00#, the default if you enter Date()
rather than Now. That is NOT the same value as #02/19/2008 15:00:36#, so I for
one would be really peeved if searching for the latter found the former!

What are you expecting? That entering any arbitrary time would find just the
date, or some other time?

You can handle date/time values in the table by using a RANGE as a criterion;
for example, if (as appears to not be the case) you had dates and times in the
table and you wanted to update all records for a selected date, you could use
a criterion like

quizDate >= Format([Enter date:], "mm\/dd\/yyyy") AND quizDate < DateAdd("d",
1, Format([Enter date:], "mm\/dd\/yyyy"))

 

[Rob W]

Apologies , my debugging is all about finding out where the problem area is.

My application (Flash) write the current date/time as a string which then
goes into my Date field (quizDate) as dd/mm/yyyy hh:mm:ss.

All I want to is update where the User is the user specified and they began
the quiz at that time then update their overall results.

The problem is I cant compare dates no matter what format I use :-(

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

This tells me there is a data mismatch, I believe the jet conenction must
make a comparison when the date is in American format only???

Cheers
Rob

Further testing shows this is ok

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

So it does'nt like the time element ... Interesting ..

Well, it DOES like it; it's just that - apparently - what's stored in the
table is actually #02/19/2008 00:00:00#, the default if you enter Date()
rather than Now. That is NOT the same value as #02/19/2008 15:00:36#, so I
for
one would be really peeved if searching for the latter found the former!

What are you expecting? That entering any arbitrary time would find just
the
date, or some other time?

You can handle date/time values in the table by using a RANGE as a
criterion;
for example, if (as appears to not be the case) you had dates and times in
the
table and you wanted to update all records for a selected date, you could
use
a criterion like

quizDate >= Format([Enter date:], "mm\/dd\/yyyy") AND quizDate <
DateAdd("d",
1, Format([Enter date:], "mm\/dd\/yyyy"))

 

[John W. Vinson]

Apologies , my debugging is all about finding out where the problem area is.

My application (Flash) write the current date/time as a string which then
goes into my Date field (quizDate) as dd/mm/yyyy hh:mm:ss.

All I want to is update where the User is the user specified and they began
the quiz at that time then update their overall results.

The problem is I cant compare dates no matter what format I use :-(

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

This tells me there is a data mismatch, I believe the jet conenction must
make a comparison when the date is in American format only???

Cheers
Rob

I guess I'm totally confused then.

If your date field is stored in a JET Date/Time datatype field, then it's
stored as a double float number, a count of days and fractions of a day
(times) since midnight, December 30, 1899. The format of the field does not
affect this at all.

What's needed for searching a date/time field in a table is a valid date
string; if it's a date literal, it must be delimited by the # character. I
have no idea what your rather strange expression

Format( '"+ textDate +"', \"#yyyy\\-mm\\-dd hh\:nn\:ss#\")")

is intended to accomplish.

If, on the other hand, your date field is a String then you must match it with
an identical String, with ' or " as the delimiter.

If your table field contains a time, your query criterion must match that time
to the exact second (and even that might fail in some cases); or it must use a
range of times containing the desired value. If your table field (as you
implied in the previous message) does NOT contain a time - or, more
accurately, has .0 as the fractional portion, corresponding to midnight - then
your criterion must likewise not contain a time.

What IS the datatype of QUizDate in the table UserOverallResults?
What ARE some sample values of the content of QuizDate?

 

[Rob W]

Thanks for helping out, the datatype is DATE/TIME

Sample data in the the datebase for 19/02/2008 15:00:36 which is definately
a date (datepicker appear when editing data).

Thanks again
Rob W

 

[Rob W]

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:-


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


1.. Data type mismatch in criteria expression SQL statement
This code:-


1.. 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 :-


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

Thanks for helping out, the datatype is DATE/TIME

Sample data in the the datebase for 19/02/2008 15:00:36 which is
definately a date (datepicker appear when editing data).

Thanks again
Rob W

 

[Douglas J. Steele]

By any chance does the user name have an apostrophe in it (such as
O'Reilly)?

Try:

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

Exagerated for clarity, that Replace statement is

Replace(UserName, "' ' ", " ' ' ")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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:-


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


1.. Data type mismatch in criteria expression SQL statement
This code:-


1.. 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 :-


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

 

[Rob W]

The name doesnt have an apostrophe in it, If i remove the date clause it
Runs without errors.

To confirm MUST my date be stored in American format to work with the Jet
Engine?

My actionscript code now formats the date as "mm/dd/yyyy hh:mm:ss" and debug
messages show this format.
However, because (I assume) my pc has it locale settings to UK in Access its
auto stored in UK format, no matter how I set the string up within my
actionscript code.

Thanks
Rob

 

[Douglas J. Steele]

In actual fact, date fields in Access are 8 byte floating point numbers,
where the integer portion represents the date as the number of days relative
to 30 Dec, 1899, and the decimal portion represents the time as a fraction
of a day. In other words, dates aren't actually stored with any format. The
issue is how Jet recognizes dates in order to convert them to that storage
format.

You may want to read what Allen Browne has at
http://www.allenbrowne.com/ser-36.html, or my September, 2003 "Access
Answers" column in Pinnacle Publication's "Smart Access". (You can download
the column, and sample database, for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html ), but yes, Jet will not
correctly handle dates in dd/mm/yyyy format unless the day is greater than
12 (since there are no months greater than 12).

 

[Rob W]

Im really confused at how I can now store the date as USA format in MS
Access as it defaults to UK format no matter how i format my date string.

 

[Douglas J. Steele]

Did you read the references I cited?

If your Regional Settings are set to dd/mm/yyyy, then dates will be
presented to you in that format, and you'll be able to type dates into bound
fields using that format. The issue is in SQL. Assuming that the correct
date is in the field, you simply use the Format function to ensure that it's
presented to Jet in a format Jet will correctly recognize. Most literature
says to use mm/dd/yyyy, but I find yyyy-mm-dd works just as well. The
"trick" is that it must be an unambiguous format: dd mmm yyyy will work as
well.

 

[Rob W]

Thanks, understood.

With my code below since textDate IS FORMATTED as a USA date won't I Have to
put the formatting condition around QuizDate??
QuizDate is stored as UK date format within the access database, therefore
comparing USA Date against a USA Date?

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

Cheers
Rob

 

[Douglas J. Steele]

QuizDate is NOT "stored as UK date format within the access database". As
I've already said, QuizDate is stored as an 8 byte floating point number, so
that it has no format whatsoever. As I'm typing this, it's 11:28 AM on 20
Mar, 2008. From the Immediate Window:

?Now()
2008-03-20 11:28:29 AM
?Format(Now(), "General Number")
39527.4782407407

20 Mar, 2008 is 39,527 days since 30 Dec, 1899, and 11:28 AM is 47.82% of a
day (Noon will be 50%)

Jet will see #2008-03-20 11:28:29# and convert it to 39527.3782407407. If
that's what happens to be stored in QuizDate, it's a match (even though you
see it as 20/03/2008 11:28:29 on your forms). Note that you may very well
run into issues with floating point round off, but that's outside of the
immediate discussion. Let's get past the error messages first!

Of course, it could very well be that Flash is introducing problems.

 

[Rob W]

Thanks, Im understanding date is not a date its a number, Im not sure how to
progress this anymore ...

This SQL runs in MS access ....

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

which Runs but UPDATES 0 Rows.

UPDATE UserOverallResults
SET Results = 30
WHERE UserName = 'Rov'
AND QuizDate = #20/02/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 follwoing reason: Parameter object is
improperly defiend. 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") = #20/02/2008
15:46:38#

The below code replicates the second SQL statement and erros 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 = #20/02/2008 15:46:38#

Im so confused.

Rob

 

[Rob W]

I re-tested the dates in MS Access using #02/20/2008 15:46:38# and they both
worked (UPDATED 0 rows and UPDATED 1 row)