 |
| Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
June 23rd, 2004, 10:40 AM
|
|
Authorized User
|
|
Join Date: Jan 2004
Location: Frederick, MD, USA.
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Date query pulling records from yesterday
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()));
|
|
June 23rd, 2004, 11:39 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
|
|
The function to get today is DATE().
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
June 23rd, 2004, 11:44 AM
|
|
Authorized User
|
|
Join Date: Jan 2004
Location: Frederick, MD, USA.
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Okay, so in my query when I say
WHERE (((DateValue([TIMESTAMP]))=Date()));
Am I using the function incorrectly?
|
|
June 23rd, 2004, 11:52 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
|
|
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
|
|
June 23rd, 2004, 12:23 PM
|
|
Authorized User
|
|
Join Date: Jan 2004
Location: Frederick, MD, USA.
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|
|
June 27th, 2004, 03:45 PM
|
|
Authorized User
|
|
Join Date: Jul 2003
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
Similar Threads
