Query DateTime field that where some cells contain NULL

[LDD]

Hi Folks

I'm trying to run a query against a DateTime field in a table.
This DateTime field does contain some NULL values.

When I run the query I get a Data type mismatch error. I've tested it
against another field that doesn't contain any NULL values and it works
fine.

Anyone know how to get around this problem?

statement:
select dtField1 from table
where dtField1 = "18/05/2005"

error: Data Type Mismatch in criteria expression

thanks folks

LDD

 

[Allen Browne]

The literal date in the SQL string needs to be delimited with #, and
formatted as m/d/y.

Try:
SELECT dtField1 FROM Table1
WHERE dtField1 = #05/18/2005#;

For more info on ensuring Access understands your dmy dates, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[LDD]

It executes but doesn't return any results.
I think because it may be a DateTime field
Actual Data may look like 18/05/2005 7:25:10 PM
But I want to return all of the dates that meet that criteria.
and the DateValue() function doesn't like the NULL either

How do

 

[xRoachx]

Hey LDD, you need to format the date/time field to only include the date
portion. FOr example:

SELECT Format([dtField1],"Short Date") AS DesiredDate
FROM Table
WHERE (((Format([dtField1],"Short Date"))=#1/23/2006#));

 

[KARL DEWEY]

Try:
SELECT dtField1 FROM Table1
WHERE Int([dtField1]) = #05/18/2005#;

 

[Rick Brandt]

It executes but doesn't return any results.
I think because it may be a DateTime field
Actual Data may look like 18/05/2005 7:25:10 PM
But I want to return all of the dates that meet that criteria.
and the DateValue() function doesn't like the NULL either

SELECT dtField1
FROM Table1
WHERE dtField1 >= #05/18/2005#
AND dtField1 < #05/19/2005#

Never apply criteria to an expression unless there is no other way to do it or
unless the table is very small. Doing so forces a table scan because no index
can be used when testing an expression.

 

[Allen Browne]

LDD, while the other answers will work, Rick's the most efficient solution.

 

[LDD]

Thanks for all your suggestions and help