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 Date Parameters in a Query with Date and Time
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. |
#2
|
|||
|
|||
Using Date Parameters in a Query with Date and Time
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" wrote in message ... 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. |
#3
|
|||
|
|||
Using Date Parameters in a Query with Date and Time
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 -- Daryl S "Alaska1" wrote: 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. |
#4
|
|||
|
|||
Using Date Parameters in a Query with Date and Time
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 "Alaska1" wrote: 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. |
#5
|
|||
|
|||
Using Date Parameters in a Query with Date and Time
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. "Alaska1" wrote in message ... 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. |
#6
|
|||
|
|||
Using Date Parameters in a Query with Date and Time
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 "Alaska1" wrote in message ... 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. |
#7
|
|||
|
|||
Using Date Parameters in a Query with Date and Time
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" wrote: 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" wrote in message ... 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. . |
#8
|
|||
|
|||
Using Date Parameters in a Query with Date and Time
On Wed, 19 May 2010 13:59:01 -0700, Alaska1
wrote: 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]) 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". -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|