Why this query does not return any record?

[gz]

Hi, the query is
Select * from Orders Where OrderDate= #1/4/2006#

There is a record with OrderDate = 1/4/2006 in the table Orders. Why this
query does not return any record? and also no error message?

I've tried:
Select * from Orders Where format( OrderDate,"mm/dd/yyyy") = #1/4/2006#

It does not work, too.

Anything wrong?

 

[Guest]

I assume that OrderDate is a DateTime datatype field. Is there a time
component with the date in the OrderDate field. That could be the problem as
time is a decimal fraction of a day added to the serial date and therefore
will not equal the date. If there is then use --
Select * from Orders Where Int([OrderDate])= #1/4/2006#
This strips off any time from the date.

 

[John Spencer]

You should be searching the datefield without formatting it. When you use
the format function on the date field, you are forcing it to be a string.
Is OrderDate a text field (storing a string that looks like a date) or is it
a date field?

Try
Select * from Orders Where OrderDate = #1/4/2006#

If that doesn't work try,
Select * from Orders Where OrderDate >=#1/4/2006# and OrderDate <#1/4/2006#
If this second method works, then you have stored a time (other than
midnight) along with date.

 

[gz]

I found it.
The date saved to table actually contains time.

 

[John Vinson]

I found it.
The date saved to table actually contains time.

THat's a common problem!

Try using a criterion

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

to find the value no matter what the time portion is.

John W. Vinson[MVP]