Returned dates always one short

[Guest]

Can anyone please explain to me why when I run a query for
between[date]and[date] I am always a day short of what I am asking for. If I
ask for 01/01/06 to 15/01/06 I get 01/01/06 to 14/01/06 it completely
mystifies me. Thanks

 

[John Spencer]

Probably because your fields have a time component also. Datetime fields
are stored as a double number where the time is after the decimal point. So
if you are looking for records on the 15th and put in just a date you will
only get the records where the time is exactly midnight.

You can use the DateValue function to force your datetime fields to only
have a data portion or better you can adjust your date criteria to

SomeField >= Date() and SomeField < YourCutoffDate + 1

 

[Douglas J. Steele]

Sounds as though your date field also includes time (you're probably using
Now to populate it, rather than Date)

Dates are stored as 8 byte floating point numbers, where the integer part
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal part represents the time as a fraction of a day. Today (2 Mar, 2006)
is 38778, whereas noon today would be 38778.5. When you don't supply a
time, therefore, anything with a time for today won't be picked up.

Simply add one to the second date. If you're prompting in a query, you can
use BETWEEN [Start Date] AND [End Date] + 1, or BETWEEN [Start Date] AND
DateAdd("d", 1, [End Date])

 

[Guest]

I thought by making them a short date and format them to 00-00-00 I was
cutting the time off?

Probably because your fields have a time component also. Datetime fields
are stored as a double number where the time is after the decimal point. So
if you are looking for records on the 15th and put in just a date you will
only get the records where the time is exactly midnight.

You can use the DateValue function to force your datetime fields to only
have a data portion or better you can adjust your date criteria to

SomeField >= Date() and SomeField < YourCutoffDate + 1

Can anyone please explain to me why when I run a query for
between[date]and[date] I am always a day short of what I am asking for.
If I
ask for 01/01/06 to 15/01/06 I get 01/01/06 to 14/01/06 it completely
mystifies me. Thanks

 

[John Vinson]

I thought by making them a short date and format them to 00-00-00 I was
cutting the time off?

Nope. The Format does not change what is STORED in the table - just
what is displayed. If you use a yy-mm-dd format, then the internally
stored values #03/02/2006# and #03/02/2006 08:22:00# and #03/02/2006
23:59:59# will all be different (and the latter two won't pass your
query criteria), even though you only SEE 06/03/02.

John W. Vinson[MVP]

 

[Guest]

Thank you I had been adding one, but not in the query, I will do that and
solve all the problems. You learn something new every day.

Sounds as though your date field also includes time (you're probably using
Now to populate it, rather than Date)

Dates are stored as 8 byte floating point numbers, where the integer part
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal part represents the time as a fraction of a day. Today (2 Mar, 2006)
is 38778, whereas noon today would be 38778.5. When you don't supply a
time, therefore, anything with a time for today won't be picked up.

Simply add one to the second date. If you're prompting in a query, you can
use BETWEEN [Start Date] AND [End Date] + 1, or BETWEEN [Start Date] AND
DateAdd("d", 1, [End Date])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Can anyone please explain to me why when I run a query for
between[date]and[date] I am always a day short of what I am asking for.
If I
ask for 01/01/06 to 15/01/06 I get 01/01/06 to 14/01/06 it completely
mystifies me. Thanks