Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / December 2005

Tip: Looking for answers? Try searching our database.

To calculate Day Count (Monday,Tuesday etc) in month in MS Access

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
sandipt - 27 Dec 2005 22:25 GMT
I want to calculate in every month how many days (like Mondays, Tuesdays etc)
are falling. Basically I have two group of People (Monday, Wednesday, Friday)
and (Tuesday, Thrusday, Saturday). And want to calculate number of days
possible for each group in a month. It varies from 12 to 14 depends on
starting and ending Day of month.
Vincent Johns - 27 Dec 2005 23:14 GMT
> I want to calculate in every month how many days (like Mondays, Tuesdays etc)
> are falling. Basically I have two group of People (Monday, Wednesday, Friday)
> and (Tuesday, Thrusday, Saturday). And want to calculate number of days
> possible for each group in a month. It varies from 12 to 14 depends on
> starting and ending Day of month.

If that's all you want to do, I suggest using Excel.  Fill a List
(Excel's version of a Table) with days of the year from 1 to 365,
convert to dates, and sort based on the day of the week.

In Access, you could do something similar (use a Table with all those
days), but I think it would take more work.  The Excel version you could
probably finish in 5 or 10 minutes.

  -- Vincent Johns <vjohns@alumni.caltech.edu>
  Please feel free to quote anything I say here.
Randy Harris - 28 Dec 2005 00:01 GMT
There are a lot of different ways of doing that.  You didn't indicate how
you wanted to use the information.  You posted in queries, but I can't think
of a way to do it in a query without a function of some sort.  Here are a
pair of functions that compute the number of days for a given month.

Public Function MWFcount( _
               ByVal yNum As Integer, _
               ByVal mNum As Integer _
               ) As Integer
   Dim I As Integer, Counter As Integer
   For I = 1 To Day(DateSerial(yNum, mNum + 1, 0))
       Select Case Weekday(DateSerial(yNum, mNum, I))
           Case 2, 4, 6: Counter = Counter + 1
       End Select
   Next I
   MWFcount = Counter
End Function

Private Function TThScount( _
               ByVal yNum As Integer, _
               ByVal mNum As Integer _
               ) As Integer
   Dim I As Integer, Counter As Integer
   For I = 1 To Day(DateSerial(yNum, mNum + 1, 0))
       Select Case Weekday(DateSerial(yNum, mNum, I))
           Case 3, 5, 7: Counter = Counter + 1
       End Select
   Next I
   TThScount = Counter
End Function

Both take the year and month as input arguments.  The first returns the
number of Mondays, Wednesdays and Fridays in that month.  The second returns
the number of Tuesdays, Thursdays and Saturdays.  You could use them in a
query that grouped by months.

HTH

Signature

Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

> I want to calculate in every month how many days (like Mondays, Tuesdays etc)
> are falling. Basically I have two group of People (Monday, Wednesday, Friday)
> and (Tuesday, Thrusday, Saturday). And want to calculate number of days
> possible for each group in a month. It varies from 12 to 14 depends on
> starting and ending Day of month.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.