now i want to count training sessions NO SHOW occured in the CURRENT MONTH. I
want this to be in a textbox and ill put it in my form.

i am using this:

=DCount("[NoShow]","Training Records","[NoShow] = True")

and it counts all NOSHOWS in my record which includes also NOSHOWs from
previous months.

i have no idea how to do this by month...i want something the same as:

=DCount("TSID","[Training Sessions]","SessionDate between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")


which count Training Session on the current month.
please help..

here is my query SQL:

SELECT [Training Sessions].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID, Count([Training Records].TraineeID) AS CountOfTraineeID, Abs
(Sum(NoShow)) AS CountNoShow
FROM [Training Sessions] INNER JOIN [Training Records] ON [Training Sessions].
TSID=[Training Records].TSID
GROUP BY [Training Sessions].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID
HAVING (((Year([SessionDate]))=Year(Date())) AND ((Month([SessionDate]))
=Month(Date())) AND ((Day([SessionDate]))=Day(Date())))
ORDER BY [Training Sessions].SessionDate DESC;



ghostman wrote:
wow! that works like a charm!
thank you very much John!

Assumption: NoShow is a Boolean (yes/no) field.

[quoted text clipped - 49 lines]
counting YTD, i can't get the THIS MONTH work - showing errors!)
Thanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200908/1