Count YTD workdays

S

Secret Squirrel

I have a table "tblWorkdaysCalendar" that lists all the work days for
2008-2011. I'm trying to count the number of workdays for 2009 YTD through
the last full month. How can I do that in my query? Right now if I select
just a year from my form it gives me all the workdays for that given year. If
I choose a month and year then I get just that month. The month & year
seleciton together works fine and returns just that month's workdays but I
can't figure out how to count workdays YTD through the last full month. Any
help would be greatly appreciated.

SELECT Count(WorkdaysCalendar.CalDate) AS CountOfCalDate,
Format([CalDate],"mmmm") AS Months, Format([CalDate],"yyyy") AS Years
FROM WorkdaysCalendar
GROUP BY Format([CalDate],"mmmm"), Format([CalDate],"yyyy")
HAVING
(((Format([CalDate],"mmmm"))=[Forms]![frmSelectAbsenteeism]![cboMonth]) AND
((Format([CalDate],"yyyy"))=[Forms]![frmSelectAbsenteeism]![cboYear])) OR
(((Format([CalDate],"yyyy"))=[Forms]![frmSelectAbsenteeism]![cboYear]) AND
(([Forms]![frmSelectAbsenteeism]![cboMonth]) Is Null));


Thanks
SS
 
D

Danny J. Lesandrini

So the question is, how do I get the last day of the current month?

I usually do one of these.

This one gives you the first day of NEXT month
WHERE [CalDate] < DateSerial(Year(Date()),Month(Date()),1)


This one gives you the last day of THIS month
WHERE [CalDate] <= DateSerial(Year(Date()),Month(Date()),1) -1

You're using the HAVING clause, but I don't see why you need it.
Actually, I don't see why you need the combo boxes. If you want
the WHERE through the end of the CURRENT month, then the
boxes are supurflous.
 
J

John Spencer MVP

For the current month and year

SELECT Count(CalDate)
FROM tblWorkdaysCalendar
WHERE CalDate Between DateSerial(Year(Date()),1,1) and
DateSerial(Year(Date()),Month(Date()),0)

Using your controls for input to get the year and month

WHERE CalDate Between DateSerial([Forms]![frmSelectAbsenteeism]![cboYear],1,1)
AND DateSerial([Forms]![frmSelectAbsenteeism]![cboYear],
[Forms]![frmSelectAbsenteeism]![cboMonth],0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top