I have an access date query that pulls data for the current day, but also sometimes returns records from yesterday. The Date field is a timestamp. Perhaps there's an error with the way the DateValue function evaluates. Any ideas? Thanks!

SELECT CONTROL_KEY, ISSUE, DateValue([TIMESTAMP]) AS DateOnly
FROM CONTROLS_ARCHIVE
WHERE (((DateValue([TIMESTAMP]))=Date()));

The function to get today is DATE().

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Okay, so in my query when I say

WHERE (((DateValue([TIMESTAMP]))=Date()));

Am I using the function incorrectly?

If TIMESTAMP is a variable you created, then yes. If you're doing it in VBA code on a form then express it this way:

"SELECT CONTROL_KEY, ISSUE, DateValue([TIMESTAMP]) AS DateOnly
FROM CONTROLS_ARCHIVE
WHERE (((DateValue([TIMESTAMP]))=#" & Date() & "#));"


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

I'm just using it in a query and placing Date() in the criteria for the DateValue([Timestamp]) field. But it still is retrieving 4 values from the previous day as well as the current day values.

Thank you for your help though. It may be an Access 2000 issue.

Justine,

It works for me. In a test db, I have a table called CONTROLS_ARCHIVE. It has the following data:

CONTROL_KEY ISSUE TIMESTAMP
----------- ----- ----------
1 ABC 6/27/2004 10:10:20
2 DEF 6/27/2004 1:01:26
3 GHI 6/26/2004 15:20:34
4 JKL 6/26/2004 23:59:59
5 MNO 6/25/2004 0:00:01
6 PQR 6/24/2004 13:20:56

I create the following query:

SELECT CONTROLS_ARCHIVE.CONTROL_KEY, CONTROLS_ARCHIVE.ISSUE, DateValue([TIMESTAMP]) AS DateOnly
FROM CONTROLS_ARCHIVE
WHERE (((DateValue([TIMESTAMP]))=Date()));

The output I get is:

CONTROL_KEY ISSUE TIMESTAMP
----------- ----- ----------
1 ABC 6/27/2004 0:00:00
2 DEF 6/27/2004 0:00:00

The database is Access 2000 ( 9.0.6926 SP-3 ).

Doesn't seem to be a problem with Access 2000; please check your data, query and database version again.

Cheers,
Prat