Dcount Date time field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 14 Nov 2005 15:25 GMT

Hi All, I am trying to create a counter based on the date and time entered
in a field. I looked up Dcount ideas in this newsgroup but it isn't
working. Here's my SQL

SELECT ZipMiles4.ShipperApptDate, ZipMiles4.ShipperApptTime,
ZipMiles4.LocationZIP, ZipMiles4.ID, ZipMiles4.Sort,
[ShipperApptDate]+nz([ShipperApptTime]) AS DT, DCount("DT","ZipMiles5","DT
<=" & [DT]) AS Ord
FROM ZipMiles4;

The Ord field returns #error. It is a Date/Time field. Please help.

Thanks,
Mike

Reply to this Message

Mike - 14 Nov 2005 15:54 GMT

I figured it out. I needed "'"&[DT]&"'" on the end.

> Hi All, I am trying to create a counter based on the date and time entered
> in a field. I looked up Dcount ideas in this newsgroup but it isn't
[quoted text clipped - 10 lines]
> Thanks,
> Mike

Reply to this Message

John Vinson - 14 Nov 2005 20:43 GMT

>I figured it out. I needed "'"&[DT]&"'" on the end.

That will work but it's doing it the hard way - converting the
date/time to a string and then doing a string comparison. The string
comparison will give you incorrect results - the text string
"9/15/2005" is GREATER than the text string "11/4/2005".

Try DCount("DT","ZipMiles5","DT <= #" & [DT] & "#")

The # character is a date delimiter.

John W. Vinson[MVP]

Reply to this Message