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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|