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_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 <at> earthlink <decimal-point> net
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/AwUBSNxcGoechKqOuFEgEQJG8QCeOxgVZK79uJum6LCbaWfLTcpoGuoAoOdp
djd+0zDtnRw1fCNYmipATYyq
=4XNu
-----END PGP SIGNATURE-----
.

Relevant Pages

  • Re: Calculating with Variable Search
    ... If C2 is a date/time stamp what is the result of this formula: ... Microsoft Excel MVP ... that timestamp. ... They look at the data in each reading column and apply the ...
    (microsoft.public.excel.newusers)
  • Re: Update syntax error?
    ... if i update the timestamp it gives a syntax error:< timestamp is Date/Time ... Note, though, that the date will be interpreted as being in month/day/year format if that interpretation results in a valid date, even if you are accustomed to day/month/year format. ...
    (microsoft.public.access.formscoding)
  • Re: How to use TIMESTAMP
    ... Timestamp is NOT a date/time representation. ... If you want to capture ... an insert/update trigger. ...
    (microsoft.public.sqlserver.server)
  • Re: Access, MyODBC, MySQL Questions .....
    ... store the current date/time in the field. ... This is different than the SQL Server Timestamp field which automatically stores a binery representation of the current date/time that is not user usable. ...
    (comp.databases.ms-access)