DCOUNT
For the current month, you can use the following expression:
DCOUNT("TSID","[Training Sessions]","SessionDate between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")
For Last Month the third argument would be
"SessionDate between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date())+1-1,0)"
The expression
DateSerial(Year(Date()),Month(Date())+1,0)
returns the last day of the month. It helps to think of this as
DateSerial(Year(Date()),Month(Date())+1,1) -1
which can be expressed as
DateSerial(Year(Date()),Month(Date())+1,1-1)
and 1-1 is zero.
'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===
ghostman via AccessMonster.com wrote:
i have this DCOUNT formula on a text box which counts number of training
sessions in my record. The text box is in my main form.
=DCount("[TSID]","Training Sessions","[TSID]")
how can i make it count the number of training sessions on the current month??
Training Session table:
TSID
ModuleName
SessionDate
(some fields..)
I am counting records from THIS MONTH & Year To Date. (I have no problem in
counting YTD, i can't get the THIS MONTH work - showing errors!)
Thanks.
|