Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
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
 
Old 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
Default 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()));
 
Old June 23rd, 2004, 11:39 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

The function to get today is DATE().

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old 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
Default

Okay, so in my query when I say

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

Am I using the function incorrectly?
 
Old June 23rd, 2004, 11:52 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old 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
Default

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.
 
Old June 27th, 2004, 03:45 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Thread Thread Starter Forum Replies Last Post
Pulling Records from Friday istcomnet ASP.NET 1.0 and 1.1 Basics 6 July 18th, 2008 04:18 PM
Pulling records from the database debjanib ASP.NET 1.0 and 1.1 Professional 2 April 17th, 2006 09:03 AM
getting records by date Toran Classic ASP Databases 6 May 1st, 2004 12:52 PM
Date based query when date is nvarchar MichaelTJ SQL Language 4 January 12th, 2004 09:57 PM
Convert String Date to Date for a SQL Query tdaustin Classic ASP Basics 4 July 7th, 2003 06:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.