Why this query does not return any record?

  • Thread starter Thread starter gz
  • Start date Start date
G

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?
 
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.
 
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.
 
I found it.
The date saved to table actually contains time.
Click to expand...

THat's a common problem!

Try using a criterion
= [Enter date:] AND < DateAdd("d", 1, [Enter date:])
Click to expand...

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

John W. Vinson[MVP]
 
Back
Top