MS Acess Date in SQL

[Taji]

Can someone please explain this to me.

When I run the following query in MS Access, it doesn't return
anything.

SELECT tbl_vdc.vcd_id, tbl_vdc.vcd_count, tbl_vdc.batch_day
FROM tbl_vdc
WHERE tbl_vdc.batch_day = #9/24/2004#;

But if I run the following query, it returns one record for
09/24/2004.

SELECT tbl_vdc.vcd_id, tbl_vdc.vcd_count, tbl_vdc.batch_day
FROM tbl_vdc
WHERE tbl_vdc.batch_day Between #9/24/2004# And #9/25/2004#;

Even though there is a record for 09/25/2004, it doesn't return it
with the above statement. To get records for the 25th, my date range
has to go pass the date of the 25th.

Is there anything wrong with the 1st query? And how come the second
query doesn't include records for the 25th?

Thanks.

Gabsaga

[Rick Brandt]

Taji wrote:

Can someone please explain this to me.

When I run the following query in MS Access, it doesn't return
anything.

SELECT tbl_vdc.vcd_id, tbl_vdc.vcd_count, tbl_vdc.batch_day
FROM tbl_vdc
WHERE tbl_vdc.batch_day = #9/24/2004#;

But if I run the following query, it returns one record for
09/24/2004.

SELECT tbl_vdc.vcd_id, tbl_vdc.vcd_count, tbl_vdc.batch_day
FROM tbl_vdc
WHERE tbl_vdc.batch_day Between #9/24/2004# And #9/25/2004#;

Even though there is a record for 09/25/2004, it doesn't return it
with the above statement. To get records for the 25th, my date range
has to go pass the date of the 25th.

Is there anything wrong with the 1st query? And how come the second
query doesn't include records for the 25th?

Your field (batch_day) likely includes both date and time data.
Actually ALL DateTime fields in Access include both a date and a time.
It's just that when you don't specify a time, midnight is assumed.

So...when you apply criteria to a DateTime field and only supply the
date as the criteria you are actually applying a criteria for records on
that date that also have midnight as the time.

Use of the BETWEEN statement is a good way to deal with this. When
doing BETWEEN statements it is a common practice to add a day to the
"end date" value so that you actually get the records that occurred on
the previous day.

On small tables you can also do something like...

WHERE DateValue(batch_day) = #9/24/2004#

That will strip the time off of the value being tested, however whenever
you test against an expression you lose the capability for Access to use
any index you might have on the field so it is not as efficient.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[almish]

ga**********@hotmail.com (Taji) wrote in message news:<15**************************@posting.google. com>...

SELECT tbl_vdc.vcd_id, tbl_vdc.vcd_count, tbl_vdc.batch_day
FROM tbl_vdc
WHERE tbl_vdc.batch_day Between #9/24/2004# And #9/25/2004#;

A Date type field contains a Date AND a Time. Comparing that field to just a
Date will not yield a match since the time portion may not match, i.e.,
#10/4/2004 12:01# does not equal #10/4/2004 12:02#.

Try using the DateValue() function to pull just the Date:

SELECT tbl_vdc.vcd_id, tbl_vdc.vcd_count, tbl_vdc.batch_day
FROM tbl_vdc
WHERE DateValue(tbl_vdc.batch_day) = #9/24/2004#;

[Taji]

mi****@execpc.com (almish) wrote in message news:<95**************************@posting.google. com>...

ga**********@hotmail.com (Taji) wrote in message news:<15**************************@posting.google. com>...

SELECT tbl_vdc.vcd_id, tbl_vdc.vcd_count, tbl_vdc.batch_day
FROM tbl_vdc
WHERE tbl_vdc.batch_day Between #9/24/2004# And #9/25/2004#;

A Date type field contains a Date AND a Time. Comparing that field to just a
Date will not yield a match since the time portion may not match, i.e.,
#10/4/2004 12:01# does not equal #10/4/2004 12:02#.

Try using the DateValue() function to pull just the Date:

SELECT tbl_vdc.vcd_id, tbl_vdc.vcd_count, tbl_vdc.batch_day
FROM tbl_vdc
WHERE DateValue(tbl_vdc.batch_day) = #9/24/2004#;

Thanks guys for all your help. That explained it all and the DateValue
function did the trick.