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