Using Date Parameters in a Query with Date and Time

  • Thread starter Thread starter Alaska1
  • Start date Start date
A

Alaska1

I am using date parameters on a field that has the date and time. When i use
the syntax Between [start date] and [end date] and select the dates from
4/19/2010 to 5/18/2010 it only gives me the data enter up to 5/17/2010 not to
5/18/2010. It give me the data for 5/18/2010 if I select the end date as
5/19/2010. I tried testing it on a standard date field with no time and it
worked fine. I am missing somthing in the syntax.
 
You're not missing anything. That's how it works!

Dates are stored as 8 byte floating point numbers, where the integer portion
represents the date as the number of days relative to 30 Dec, 1899 and the
decimal portion represents the time as a fraction of a day. When you only
have a date, it's that number with no decimal portion. When you have a time
on that date, it's larger than just the date.

Try using

Between [start date] and DateAdd("n", 86399, [end date])

or, perhaps easier to understand,

MyDateField >= [start date] AND MyDateField < DateAdd("d", 1, [end date])
 
Alaska1 -

The date portion of a date/time field is the 'integer' portion of the
number, where the time is the 'decimal' fraction of the number. So if you
are using Between, then you will miss all the hours after midnight on the end
date. You can instead use something like this:
= [StartDate] AND < [EndDate]+1
Click to expand...

--
Daryl S


Alaska1 said:
I am using date parameters on a field that has the date and time. When i use
the syntax Between [start date] and [end date] and select the dates from
4/19/2010 to 5/18/2010 it only gives me the data enter up to 5/17/2010 not to
5/18/2010. It give me the data for 5/18/2010 if I select the end date as
5/19/2010. I tried testing it on a standard date field with no time and it
worked fine. I am missing somthing in the syntax.
Click to expand...
 
Not in the syntax, but in how date/time fields are stored. Your query is
working correctly. By using the date part only, what you are asking for is
4/19/2010 12:00:00AM through 5/18/2010 12:00:00AM. If you want to get all
the records for 5/18/2010, regardless of time, you should just use 5/19/2010.

HTH
 
If you want to compare dates to dates, you need dates.

If your field(s) hold Date/Time values (e.g., 2:37 PM on 5/18/2010), then
you'll need to get (only) the date portion of that field, using the
DateValue() function. Then you can compare your parameters as apples to
apples (or dates to dates)...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Date/Time data types are stored as a decimal number. The number to the left
of the decimal is the number of days between your date and a reference date.
The number to the right of the decimal represent the time as a fraction of
twent-four hours. So from your description in your post, your 5/18 data ALL
looks like:
XXXX.yyyy
where yyyy in each record has a value greater than 0. So, when you enter
your parameter of 5/18/2010, Access sees it as XXXX with no decimal value.
Since all your data for 5/18 has a decimal data, all your 5/18 data is
greater than the 5/18/2010 parameter and therefore does not get returned ny
your query.

Steve
(e-mail address removed)
 
Thank you!

Neither one of this seemed to work. maybe i am not putting it into the
query correctly.
Between [start date] and DateAdd("n", 86399, [end date])
ClericalAssignment Date >= [start date] AND ClericalAssignment Date <
DateAdd("d", 1, [end date])

Douglas J. Steele said:
You're not missing anything. That's how it works!

Dates are stored as 8 byte floating point numbers, where the integer portion
represents the date as the number of days relative to 30 Dec, 1899 and the
decimal portion represents the time as a fraction of a day. When you only
have a date, it's that number with no decimal portion. When you have a time
on that date, it's larger than just the date.

Try using

Between [start date] and DateAdd("n", 86399, [end date])

or, perhaps easier to understand,

MyDateField >= [start date] AND MyDateField < DateAdd("d", 1, [end date])

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Alaska1 said:
I am using date parameters on a field that has the date and time. When i
use
the syntax Between [start date] and [end date] and select the dates from
4/19/2010 to 5/18/2010 it only gives me the data enter up to 5/17/2010 not
to
5/18/2010. It give me the data for 5/18/2010 if I select the end date as
5/19/2010. I tried testing it on a standard date field with no time and
it
worked fine. I am missing somthing in the syntax.
Click to expand...


.
Click to expand...
 
Thank you!

Neither one of this seemed to work. maybe i am not putting it into the
query correctly.
Between [start date] and DateAdd("n", 86399, [end date])
ClericalAssignment Date >= [start date] AND ClericalAssignment Date <
DateAdd("d", 1, [end date])
Click to expand...

If the name of the field in fact contains a blank, you must use square
brackets around it: [ClericalAssignment Date]

It would help if you would open the query in SQL view and post the entire SQL
string, and indicate in what manner it "didn't work".
 
Back
Top