DateValue problem in Access 2000

T

Tom

Hi

I have an MSDE table joined to an Access front end. A field called
[DATE_TIME] holds the date and time (including hrs mins and seconds) that an
action took place. I need to run a query so that I can report on a range of
dates (days, not times). In the past, the expression "between [START_DATE]
and [END_DATE]" has worked fine on Access tables. However, this is the first
time I have run this query on date and time fields, and on MSDE. When I
enter a start and end date of, say 1/4/5 to 30/4/5 (UK format), data is
returned for 1st April to 29th April. It seems to run one day short. This
means that if I want one day only, such as 1st April, I have to enter 1/4/5
to 2/4/5. I cannot expect my users to live with this. I have tried using the
DateValue expression to convert the date field to a variable called
[ACT_DATE] (actual date) but this has had no effect. Can anyone help with
this? Outputting to a temporary table is not an option I want to consider
unless I really have to.

Regards

Tom
 
D

Douglas J Steele

Dates are stored as 8 byte floating point numbers, where the integer portion
represents the number of days relative to 30 Dec, 1899, and the decimal
portion represents the time as a fraction of day.

30 April, 2005 is stored as 38472. 6 AM on 30 April, 2005 would be 38472.25.
Since 38472.25 is not less than or equal to 38472, you would get it
included.

You say tried to convert the date field using DateValue: how did you do
that?

One option would be to use "between [START_DATE] and ([END_DATE] + 1)" or
"between [START_DATE] and DateAdd("d", 1, [END_DATE])". That's actually
probably more efficient, since the calculation is only done once, as opposed
to applying the DateValue function to each row in the table.
 
T

Tom

Thanks Doug

Option 2 you suggested works a treat!

Regards

Tom

Douglas J Steele said:
Dates are stored as 8 byte floating point numbers, where the integer
portion
represents the number of days relative to 30 Dec, 1899, and the decimal
portion represents the time as a fraction of day.

30 April, 2005 is stored as 38472. 6 AM on 30 April, 2005 would be
38472.25.
Since 38472.25 is not less than or equal to 38472, you would get it
included.

You say tried to convert the date field using DateValue: how did you do
that?

One option would be to use "between [START_DATE] and ([END_DATE] + 1)" or
"between [START_DATE] and DateAdd("d", 1, [END_DATE])". That's actually
probably more efficient, since the calculation is only done once, as
opposed
to applying the DateValue function to each row in the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Tom said:
Hi

I have an MSDE table joined to an Access front end. A field called
[DATE_TIME] holds the date and time (including hrs mins and seconds) that an
action took place. I need to run a query so that I can report on a range of
dates (days, not times). In the past, the expression "between
[START_DATE]
and [END_DATE]" has worked fine on Access tables. However, this is the first
time I have run this query on date and time fields, and on MSDE. When I
enter a start and end date of, say 1/4/5 to 30/4/5 (UK format), data is
returned for 1st April to 29th April. It seems to run one day short. This
means that if I want one day only, such as 1st April, I have to enter 1/4/5
to 2/4/5. I cannot expect my users to live with this. I have tried using the
DateValue expression to convert the date field to a variable called
[ACT_DATE] (actual date) but this has had no effect. Can anyone help with
this? Outputting to a temporary table is not an option I want to consider
unless I really have to.

Regards

Tom
Click to expand...
Click to expand...
 
Top