Date range problem (1 Viewer)

[kellyboy]

We are a charity and we have a simple data base to keep track of how many people we help and how much we pay out in food vouchers. I have several reports programmed with date range parameters, i.e. I enter a first date, then a second date, and the report gives me the results. Problem is, on some of these reports, in order to include the actual period I want, I have to add one day to the end date. For example if I want a list of new people who came in during January, if I input the first date as Jan 1 and the end date as Jan 31, the report will not include people who came in on Jan 31. I have to input Feb 1 as the end date. Fair enough, but on other reports, I don’t have to do this, as the results will include the actual end date. Yet the date parameters on the queries read exactly the same.
Example 1: This query provides the value of vouchers paid out between the two dates, and the results always include the end date that I enter.
PARAMETERS [Enter First Date] DateTime, [Enter Last Date] DateTime;
SELECT Visits.Date, Visits.Value, Visits.[Store Voucher], Visits.[Key Contact Number], KeyContact.LastName, KeyContact.FirstName, KeyContact.AutoNumber, Visits.Comments
FROM Visits INNER JOIN KeyContact ON Visits.[Key Contact Number]=KeyContact.AutoNumber
WHERE (((Visits.Date) Between [Enter First Date] And [Enter Last Date]))
ORDER BY Visits.Date;

Example 2: This query provides a list of new people who were helped between the two dates, but for some reason I have to add one day to the end date to get results for that date.
PARAMETERS [Enter First Date] DateTime, [Enter Last Date] DateTime;
SELECT KeyContact.AutoNumber, KeyContact.Category, KeyContact.LastName, KeyContact.FirstName, KeyContact.[Date Added], KeyContact.DOB, KeyContact.Address1
FROM KeyContact
WHERE (((KeyContact.[Date Added]) Between [Enter First Date] And [Enter Last Date]));


Why do these queries behave differently in terms of the end date of the range? It’s driving me crazy. Our original programmer is long gone and I am trying to learn as much as I can about Access. I am using Access 2007, but the db has to be compatible with Access 2002, because we have different versions on different machines.

 

[boblarson]

If a date field is capturing and using TIME then you need to understand what passing the date but not the time will do. If you say

Between #12/24/2012# And #12/25/2012#

on a date field that does not use time also then it is going to be looking at the date only. But if you use that statement on a field which has a time element included, even if you don't type it explicitly as such, the effect of not typing it explicitly with the time is this:

Between #12/24/2012 12:00:00 AM# And #12/25/2012 12:00:00 AM#

which means you would only get any dates that happend on December 25th if there happened to be one exactly at 12 midnight including the seconds at straight up zero. So when you want to include that other date you would need to either use

Between #12/24/2012 12:00:00 AM# And #12/25/2012 11:59:59 PM#

or

Between #12/24/2012 12:00:00 AM# And #12/26/2012 12:00:00 AM#
(translated to
Between #12/24/2012# And #12/26/2012# if you pass only the date)

So that is why it does what it does.

 

[kellyboy]

Thanks Bob. I follow what you are saying but at this stage of my learning I am not aware of how to find out if the date fields in our data base are "capturing and using time". We have no need for the time element, just the date. I have looked at the properties of all the date ranges and they all seem to be the same. What should I be looking for, and where?

.

If a date field is capturing and using TIME then you need to understand what passing the date but not the time will do. If you say

Between #12/24/2012# And #12/25/2012#

on a date field that does not use time also then it is going to be looking at the date only. But if you use that statement on a field which has a time element included, even if you don't type it explicitly as such, the effect of not typing it explicitly with the time is this:

Between #12/24/2012 12:00:00 AM# And #12/25/2012 12:00:00 AM#

which means you would only get any dates that happend on December 25th if there happened to be one exactly at 12 midnight including the seconds at straight up zero. So when you want to include that other date you would need to either use

Between #12/24/2012 12:00:00 AM# And #12/25/2012 11:59:59 PM#

or

Between #12/24/2012 12:00:00 AM# And #12/26/2012 12:00:00 AM#
(translated to
Between #12/24/2012# And #12/26/2012# if you pass only the date)

So that is why it does what it does.

 

[boblarson]

You need to go into the table in DESIGN view and select the field. Look at the FORMAT property of that field and see if it says General Date or nothing at all. To use Date only you would use Short Date, Medium Date, or Long Date.

 

[kellyboy]

Bob, the relevant dates on both tables are formatted as medium date. Yet one report is definitely acting like it has the time element. This gets curiouser and curiouser....

You need to go into the table in DESIGN view and select the field. Look at the FORMAT property of that field and see if it says General Date or nothing at all. To use Date only you would use Short Date, Medium Date, or Long Date.

 

[boblarson]

If the table's field has dates with the time entered before the format was selected, it will still need it even though it isn't being used.

 

[kellyboy]

Thank you Bob. It looks like your last post has got me on the right track. I looked at my main form in datasheet view and found that the entries in the "date added" column were all listed as #name? even though they appeared as normal dates in form view. So I went back into form view and clicked on the "date added" field and sure enough, the entries expanded into date and time when I clicked on them, even though it is formatted as date only. I guess it's not possible to undo the time element now without re-entering all the dates in a new date only field. Elsewhere on this forum I learned about the DateAdd function, so I have inserted DateAdd (d,1 before [enter Last Date] and my reports are now accurately including data the end date of my range. So once again thanks for your help. This is a great forum and I know I'm going to learn a lot here.

 

[AceBK]

I ran into this problem and used this in the criteria field and found that it worked. This will included the end date in the parameter. I know I am adding to a really old thread, but this might help someone when they search.

Between [Enter Start Date] And ([Enter End Date] & " 11:59:59 PM")