Date range for a query

[Cheese]

I have set up this criteria for a date range within a query. Between [Enter
start date using format mm/dd/yyy] And [Enter end date using format
mm/dd/yyyy]. I cannot get the query or the report to pull the final date,
for example if the end date is 5/18/2007, the query will pull everything up
to 5/18/2007. Is this because I am using the term "Between"? What should I
use?
Cheese

 

[KARL DEWEY]

I would bet that your dates also have time in them. Your date parameter is
not including and data that is after midnight which has a decomal fraction
added to indicate the time.
Two solutions - add an extra day.
- remove the fraction from the data -
Int([YourDateField])

 

[Roger Carlson]

If your actual date values include the time, by putting only the date in,
you mean midnight of that date, so you lose anything on the last date.

Change the WHERE condition of your query to something like this:

WHERE DateValue([YourDateField]) Between [Enter start date using format
mm/dd/yyyy] And [Enter end date using format mm/dd/yyyy])

Of course, replace [YourDateField] with your actual field.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Douglas J. Steele]

Sounds as though you've populated the field in your table with both Date and
Time (perhaps you're using the Now() function, rather than the Date()
function?)

Quickest fix is to use

Between [Enter start date using format mm/dd/yyy] And ([Enter end date using
format mm/dd/yyyy] + 1)

or

Between [Enter start date using format mm/dd/yyy] And DateAdd("d", 1, [Enter
end date using format mm/dd/yyyy])

 

[Joan Wild]

Probably because your data is actually storing both the date and time. Between..And..5/18/2007 will get everything up to midnight the morning of 5/18/2007. If your data includes the time, then it'll miss anything on that date.

Either don't store the time in the field, or change your criteria. Also you should realize that what the user enters for the prompt will be interpreted according to the Regional Settings on the user's computer. If they enter 5/12/2007 that will be interpreted as Dec 5 if their computer is set to dd/mm/yyyy. See Allen Browne's site for working with Dates:
http://www.allenbrowne.com/ser-36.html