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_variable" 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(classdate)) As EarliestClass FROM Attendance
--
MGFoster:::mgf00 <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/AwUBSNxcGoechKqOuFEgEQJG8QCeOxgVZK79uJum6LCbaWfLTc poGuoAoOdp
djd+0zDtnRw1fCNYmipATYyq
=4XNu
-----END PGP SIGNATURE-----