Duane-
My method will definitely be more efficient if there's an index on DateClosed.
Using the function will force a table scan.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane Hookom
Sent: Wednesday, August 03, 2011 6:16 PM
To: Access Professionals Yahoo Group
Subject: RE: [MS_AccessPros] Strange date problem
John's SQL might be more efficient but another alternative is to use DateValue()
to remove the time component:
WHERE DateValue([DateClosed]) = #8/2/2011#
Duane Hookom
MS Access MVP
To: MS_Access_Professionals@yahoogroups.com
From: john@viescas.com
Date: Wed, 3 Aug 2011 17:34:35 +0200
Subject: RE: [MS_AccessPros] Strange date problem
Dear Wonder-
If there is also a Time component in your date/time field, then you won't get
any matches unless there's a record exactly at midnight. Try this:
WHERE [DateClosed] >= #8/2/2011# And [DateClosed] < #8/3/2011#
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of WonderBoyMI
Sent: Wednesday, August 03, 2011 4:34 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Strange date problem
Good morning everyone;
I'm having an issue which has me banging my head against my desk (I'm sure we
all do this regularly!)
Anyway, I have a query with only two fields. [ScheduleId] and [DateClosed]
My manager wants me to make him a report showing all of the IDs from yesterday,
but when I put #8/2/2011# in criteria I get no results. However, there are IDs
from yesterday, and if I do a range that includes yesterdays date I get them.
What am I doing wrong?
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar