DCount

[Mark]

Hello all,

My between date is not working and I guessing my syntax is off. Grrrr
syntax! The cal1 and 2 is from two calendars on my form. Thanks!

vSql = DCount("WORK_REQ_ID", "VIAWARE_WCS_TO_VIA_T", "DTIMEMOD Between #" &
cal1 & "# And #" & cal2 & "#")

 

[Rick Brandt]

Hello all,

My between date is not working and I guessing my syntax is off. Grrrr
syntax! The cal1 and 2 is from two calendars on my form. Thanks!

vSql = DCount("WORK_REQ_ID", "VIAWARE_WCS_TO_VIA_T", "DTIMEMOD
Between #" & cal1 & "# And #" & cal2 & "#")

Syntax looks good to me. Does it work if you substitute hard values for
cal1 and cal2?

 

[Mark]

Yes, it does. This works

vStr = DCount("WORK_REQ_ID", "VIAWARE_WCS_TO_VIA_T", "DTIMEMOD Between
#07/01/2005# And #07/24/2005#")

 

[Rick Brandt]

Yes, it does. This works

vStr = DCount("WORK_REQ_ID", "VIAWARE_WCS_TO_VIA_T", "DTIMEMOD Between
#07/01/2005# And #07/24/2005#")

Then cal1 and cal2 must not contain valid date values or else not the date
values you think they do.

 

[Mark]

I used a text box instead of Cal and it did work, so looks like some format
issue.

However, I'm still getting some errors in my data from the field being (in
the table) a combination of Date and time. Ex: 07/25/2005 5:09:35 PM

When I run:
vStr = DCount("WORK_REQ_ID", "VIAWARE_WCS_TO_VIA_T", "DTIMEMOD = #07/13/2005#"

It shows none. What would I use to only see the date in a field?

 

[Rick Brandt]

I used a text box instead of Cal and it did work, so looks like some
format issue.

However, I'm still getting some errors in my data from the field
being (in the table) a combination of Date and time. Ex: 07/25/2005
5:09:35 PM

When I run:
vStr = DCount("WORK_REQ_ID", "VIAWARE_WCS_TO_VIA_T", "DTIMEMOD =
#07/13/2005#"

It shows none. What would I use to only see the date in a field?

There's the "easy but inefficient" method...
vStr = DCount("WORK_REQ_ID", "VIAWARE_WCS_TO_VIA_T", "DateValue(DTIMEMOD) =
#07/13/2005#"

And there's the more difficult but more efficient method...
vStr = DCount("WORK_REQ_ID", "VIAWARE_WCS_TO_VIA_T", "DTIMEMOD BETWEEN
#07/13/2005# AND #7/14/2005#")

If you think there's any chance of having records with exactly midnight then
you have to use...
vStr = DCount("WORK_REQ_ID", "VIAWARE_WCS_TO_VIA_T", "DTIMEMOD BETWEEN
#07/13/2005# AND #7/13/2005 23:59:59#")

The efficiency issue is that you (whenever possible) want to apply criteria
to a field directly rather than to an expression containing the field. The
latter prevents the database engine from being able to utilize an index
forcing a row by row table scan.