DCount

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark - 26 Jul 2005 14:02 GMT

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 & "#")

Reply to this Message

Rick Brandt - 26 Jul 2005 14:14 GMT

> Hello all,
>
[quoted text clipped - 3 lines]
> 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?

Signature

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

Reply to this Message

Mark - 26 Jul 2005 14:21 GMT

Yes, it does. This works

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

> > Hello all,
> >
[quoted text clipped - 6 lines]
> Syntax looks good to me. Does it work if you substitute hard values for
> cal1 and cal2?

Reply to this Message

Rick Brandt - 26 Jul 2005 14:27 GMT

> 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.

Signature

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

Reply to this Message

Mark - 26 Jul 2005 15:10 GMT

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?

> > Yes, it does. This works
> >
[quoted text clipped - 3 lines]
> Then cal1 and cal2 must not contain valid date values or else not the date
> values you think they do.

Reply to this Message

Rick Brandt - 26 Jul 2005 15:36 GMT

> I used a text box instead of Cal and it did work, so looks like some
> format issue.
[quoted text clipped - 8 lines]
>
> 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.

Signature

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

Reply to this Message