A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using Between...And with General Date



 
 
Thread Tools Display Modes
  #1  
Old April 30th, 2010, 09:08 PM posted to microsoft.public.access.queries
mewins
external usenet poster
 
Posts: 26
Default Using Between...And with General Date

I am trying to query all records created between given dates. The field,
DtRecCrt, is a general date field that is set to =Now() whenever the records
are created. However, when I run the query and have the criteria for
DtRecCrt set to "between [start date] and [end date]," it will return records
up to but not including the end date specified. How do I make the query
include records created on the end date? Thanks
  #2  
Old April 30th, 2010, 10:01 PM posted to microsoft.public.access.queries
fredg
external usenet poster
 
Posts: 4,386
Default Using Between...And with General Date

On Fri, 30 Apr 2010 13:08:04 -0700, mewins wrote:

I am trying to query all records created between given dates. The field,
DtRecCrt, is a general date field that is set to =Now() whenever the records
are created. However, when I run the query and have the criteria for
DtRecCrt set to "between [start date] and [end date]," it will return records
up to but not including the end date specified. How do I make the query
include records created on the end date? Thanks



Your Date Field includes a Time value.
Therefore any data past 00:00:00 AM of the [EndDate] is not within the
Between [FromDate] And [EndDate] criteria.
Easiest work-around is to just add one day manually to the [EndDate]
when prompted, i.e. instead of entering 11/24/2005 enter 11/25/2005.

A better work-around would be to set
[FromDate]
and
[EndDate]
and their expected Datatype as query parameters in the Query Parameter
Dialog (Click Query + Parameter).
Then change your criteria to:
Between [FromDate] and ([EndDate] + 1)

The best solution, if the Time of day is not necessary, is to run an
Update Query to remove the time value from the date:

Update YourTable Set YourTable.[DateField] = DateValue([DateField]);

Then make sure the time is not included in any new entries, i.e. use
Date() instead of Now().

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
  #3  
Old April 30th, 2010, 10:02 PM posted to microsoft.public.access.queries
Tracy
external usenet poster
 
Posts: 200
Default Using Between...And with General Date

Hello,

Try using this as your criteria -

=#1/1/2010# And =Now()


Put your start date between the '#' signs. That's how I search in my
database and it includes the start date I selected and any records that have
the current date on it.

I hope that helps!



"mewins" wrote:

I am trying to query all records created between given dates. The field,
DtRecCrt, is a general date field that is set to =Now() whenever the records
are created. However, when I run the query and have the criteria for
DtRecCrt set to "between [start date] and [end date]," it will return records
up to but not including the end date specified. How do I make the query
include records created on the end date? Thanks

  #4  
Old April 30th, 2010, 10:14 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Using Between...And with General Date

mewins wrote:

I am trying to query all records created between given dates. The field,
DtRecCrt, is a general date field that is set to =Now() whenever the records
are created. However, when I run the query and have the criteria for
DtRecCrt set to "between [start date] and [end date]," it will return records
up to but not including the end date specified. How do I make the query
include records created on the end date?


That's to be expected because the time part from Now is
after the time part (midnight) in [end date].

To deal with that change your criteria to:
=[start date] And DateAdd("d", 1, [end date])

--
Marsh
MVP [MS Access]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.