Dates don't match 1

[OrthoDocSoft]

Folks,

I have a table "testsdrawn" with a column named "DateDrawn" which holds the "date and time" data type. The date "7/12/2009" is in all three records, and was written there by:

MyRecordset.Fields("DateDrawn") = Date

which works well.

Then I try to query the DB for records like this:

strSQL = "SELECT * FROM testsdrawn WHERE PatientID... blah blah " AND DateDrawn = " & Date

MyRecordSet.Open(strSQL), MyConnection, adoOpenKeyset _
adLockOptimistic

When I delete the date part, the query works fine and returns the correct PatientID, so that part is fine. When I add the lines "AND DateDrawn = " & Date," I get zero records returned (and no sql query errors).

"Date" is a function that returns today's date, and the dates in my column are all of "date and time" type, and are today's date.

Why doesn't this query find them?

Thanks,

Ortho

"you cain't fix 'stupid'...

 

[r937]

use the Access Date() function instead

SELECT [i]columns[/i] 
  FROM testsdrawn 
 WHERE PatientID... blah blah 
   [blue]AND DateDrawn >= Date()
   AND DateDrawn  < DateAdd("dd",1,Date())[/blue]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[dhookom]

Another option is this solution which corrects for different date formats:

strSQL = "SELECT * FROM testsdrawn WHERE PatientID... blah blah " AND DateValue(DateDrawn) = #" & Format(Date, "mm\/dd\/yyyy") & "#"

Duane
Hook'D on Access
MS Access MVP

 

[OrthoDocSoft]

dhookom,

your solution worked like a charm.

r937,

I had lots of formating problems using yours in VB6 (which I failed to tell you about), so it didn't work, but I appreciate you trying.

Star for dhookom.

Thanks, folks

Ortho

"you cain't fix 'stupid'...

 

[PHV]

Why not simply this ?
strSQL = "SELECT * FROM testsdrawn WHERE PatientID... blah blah AND DateDrawn=Date()"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

I would recommend against using ...

Where [YourDateField] [red][b]=[/b][/red] Date()

as dates can contain TIME values as well, making this expression FALSE.

Rather...

Where Int([YourDateField]) = Date()

will include only the DATE component of your field.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}