Criteria on date/time field

[Guest]

I'm having problems putting criteria using a date/time field in a Query. The
field is just date time fomat so values are output like

06/09/2005 14:24:50

I want to add a simple criteria to say >= a date e.g

I tried >= #01/09/2005#

but the Query just doesn't run. So I tried adding the time

=#01/09/2005 00:00:00#

Access just got rid of the time data and still no success.

The Query simply doesn't run, I get no error message.

A colleague has suggested

=DateSerial(2005,9,5) And <DateSerial(2005,10,2)

but why is it happening and why this workaround is necessary. is there a
better
way?

I'm using Access 2000 (9.0.6929 SP-3) on a Windows XP SP-1 Dell PC.

Matt

 

[Duane Hookom]

The date must be in the format of m/d/yyyy.

When you state "just doesn't run", do you mean no results are returned or
the datasheet view doesn't appear?

 

[Guest]

Thnaks Duane, but I'm in the UK and the Regional Settings on the machine are
UK based so why would I need that date format? I tried it anyway, and it
still didn't run.

When I say it didn't run, the datasheet view doesn't appear, it just stays
in Design view.

 

[Duane Hookom]

Queries expect the U.S. date format.

If the query doesn't display in datasheet view, you have bigger issues. How
about sharing your SQL View?

 

[Guest]

Sure:

SELECT TblData.CRN, TblData.ReceiptDate, TblData.GPPriority
FROM TblData
WHERE (((TblData.ReceiptDate)>=#9/1/2005#));

Just sits there in design view.

That's it!

Matt

 

[Duane Hookom]

I would copy the sql to the clipboard, close the existing query, create a
new query, view the sql, paste from the clipboard, and see what happens.