Graham-
Good suggestion. Note that the performance hit may be quite large if the query has been dependent on an index built on the Date/Time field. Using the function negates use of the index.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Graham Mandeno
Sent: Wednesday, October 23, 2013 12:27 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Strange one on dates in query
Hi Phil
Another option is to use the DateValue() function which removes any time portion from a date/time value. This is handy in particular if you are looking for records on a particular date:
DateValue([DateTimeField]) = #some date#
seems more intuitive than:
([DateTimeField] >= #some date#) and ([DateTimeField] < #some date# + 1)
There *may* be a performance hit because you are calling a finction for every record, but I shouldn't think it would be noticeable unless you have huge numbers of records.
Best wishes,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Phil Knowles
Sent: Wednesday, 23 October 2013 00:35
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Strange one on dates in query
Hi John
Many thanks for that.
I will have to go through all my dates and queries etc and consider the implications of all this.
Oh Joy!
Phil
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, 22 October 2013, 12:17
Subject: RE: [MS_AccessPros] Strange one on dates in query
Phil-
1) Yes.
2) No. BETWEEN does >= low date AND <= high date. If there are several records that contain time stamps that match the high date, you won't get them. Best to do:
>= #low date# And < (#high date# + 1)
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Phil Knowles
Sent: Tuesday, October 22, 2013 12:47 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Strange one on dates in query
Hi John
Spot on - some of my dates in this table have time stamps and others don't.
So in my example
>= #01/08/13# will include all records from 1st August regardless of time stamp?
Can I assume that the date criteria of between ..... and ........ will work correctly regardless of time stamp?
cheers
Phil
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, 22 October 2013, 10:55
Subject: RE: [MS_AccessPros] Strange one on dates in query
Phil-
A Date/Time field can contain a date part, a time part, or both. I suspect some of your records have a time part recorded. July 31, 2013 10:00:00 is a greater value than July 31, 2013. Try using >=#07/01/2013# (or #01/07/2013# if you're on a non-US machine and in the query window).
To see the full value, you will have to change the Format property of the field to see both the date and time components.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of pdk444444@yahoo.co.uk
Sent: Tuesday, October 22, 2013 11:42 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Strange one on dates in query
Hi Guys
I was just creating a simple query and used > date in the criteria of a date field and records with that date appeared on the query.
Specifically, I used >#31/07/13# to include only records from 1/8 onwards but records for 31/7 appeared - but not all the records for that date !!
Obviously > date should exclude the records on that particular date.
What could cause this to happen?
Phil
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (7) |
Tidak ada komentar:
Posting Komentar