DCount distinct date/time based on date only

[Cron]

Hi I'm trying to Dcount *unique* records by comparing a date/time
field. I say *unique* because the field contains a date/time but I
need to ignore the timestamp and work off the date only.

I think the code below should work to count unique values in a normal
text field but the date/time is causing a lot of problems because it's
reading the timestamps to be different despite the "medium date"
formatting.

DCount("[classdate]", "attendance ", "classdate IN ( SELECT
FIRST(classdate ) FROM attendance GROUP BY classdate)")


Anyone got any ideas?
Thanks,
Ciarán

[MGFoster]

Re: DCount distinct date/time based on date only

Cron wrote:

Hi I'm trying to Dcount *unique* records by comparing a date/time
field. I say *unique* because the field contains a date/time but I
need to ignore the timestamp and work off the date only.
>
I think the code below should work to count unique values in a normal
text field but the date/time is causing a lot of problems because it's
reading the timestamps to be different despite the "medium date"
formatting.
>
DCount("[classdate]", "attendance ", "classdate IN ( SELECT
FIRST(classdate ) FROM attendance GROUP BY classdate)")

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Don't use FIRST() & LAST() 'cuz they don't work the way most people
think they are supposed to. Try the DateValue() function if you want to
get just the date part of a DateTime value:

DCount("[classdate]", "attendance ", "classdate = " &
DateValue(date_ variable) )

Where the "date_varia ble" is an actual date value or a DateTime data
type variable.

If you want to find the the earliest date use the MIN() function. If
you want to find the latest date use the MAX() function. You'll have to
use a SELECT statement when using the MIN/MAX functions:

SELECT MIN(classdate) As EarliestClass FROM Attendance

or

SELECT MIN(DateValue(c lassdate)) As EarliestClass FROM Attendance

--
MGFoster:::mgf0 0 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSNxcGoechKq OuFEgEQJG8QCeOx gVZK79uJum6LCba WfLTcpoGuoAoOdp
djd+0zDtnRw1fCN YmipATYyq
=4XNu
-----END PGP SIGNATURE-----

[Cron]

Re: DCount distinct date/time based on date only


Thanks for the reply MG but that's not really what I'm trying to
do....
Ok I've figured out how to return distinct datetime results based on
the day:
SELECT DISTINCT Format(classdat e, 'mm/dd/yyyy') as class_date FROM
attendance

Now can anyone tell me how to get this into a Dcount function for VBA?
I was hoping this would work but access doesn't like a query in the
domain paramater:
DCount("classda te", "SELECT DISTINCT Format(classdat e, 'mm/dd/yyyy')
as class_date FROM attendance", "")

Thanks a lot,
Ciarán

[Cron]

Re: DCount distinct date/time based on date only

If anyone's interested in my solution to this problem, I ended up
saving the statement above as a query and then counting the results it
generates with the DCount function from VBA.

Ciarán