Calculating the number of each day, i.e. Sunday, Monday, Tuesday, etc. in a given mon

[Eljefegeneo]

Using Access 2010, I would like to be able to count the number of each day, Sunday, Monday, Tuesday in a given month. This count will be used to calculate a total amount due for each month. That is, if a client advertises only on Mondays, I want to be able to calculate the number of Mondays this month times the cost per day. I know that I can make an Excel table for each year or more with the dates and what day of the week it is, but when I bring this into Access as a table, I can't figure out how to make the calculation. I have created a simple query for this, but it will only calculate individual days, not the total for the month. I can sum the results, but I can't bring that sum over to a form.
Ideally, I would to have a calculated field in a form (my billing form) that would show how many days per month for a particular month (January 2013, etc.) that I need to bill each client. But any suggestions would be greatly appreciated.

 

[Isskint]

Use this function to return a variant showing number of selected weekday in month of supplied date. Sunday is seen as first day of week. Typical use would be

Variable = Function DayCountPerMonth(DateConcerned,vbMonday)' returns count of Mondays in that month

Function DayCountPerMonth(SourceDate As Date, DayOfWeekToReturn As Integer) As Integer
Dim iFirstDayInMonth, iLastDayInMonth, lp As Integer
Dim DaysInMonth(6) As Integer
iFirstDayInMonth = Weekday(DateSerial(Year(SourceDate), Month(SourceDate), 1)) 'get weekday for first of month
iLastDayInMonth = Day(DateSerial(Year(SourceDate), Month(SourceDate) + 1, 0)) 'get day for last day of month
For lp = 0 To 6
    DaysInMonth(lp) = 4
Next lp
Select Case iLastDayInMonth
    Case 29
        DaysInMonth(iFirstDayInMonth - 1) = 5
    Case 30
        DaysInMonth(iFirstDayInMonth - 1) = 5
        DaysInMonth(iFirstDayInMonth) = 5
    Case 31
        DaysInMonth(iFirstDayInMonth - 1) = 5
        DaysInMonth(iFirstDayInMonth) = 5
        DaysInMonth(iFirstDayInMonth + 1) = 5
End Select
DayCountPerMonth = DaysInMonth(DayOfWeekToReturn - 1)
End Function

 

[Eljefegeneo]

Thank you for your prompt answer. However, while I understand some VBA code, I cannot figure out what to do with the above. I know I should paste the function in a module and save it to the same DayCountPerMonth. But I am not sure if I should make any changes to it before I use it. Is there anything in the code, ie. Like SourceDate that I should change to my date field ( the day of the year), or not. Or do I use it in a query, i.e. SourceDate: MyTableField with selection criteria.
Likewise, the [FONT=&quot]Variable = Function DayCountPerMonth(DateConcerned,vbMonday)'. How do I use this, in a query or on the form?[/FONT]
[FONT=&quot]What would be most advantageous is for me to just have a calculated field in the Access form for each day of the week that counts the number of that particular day of the month in question, in my case, month+2: If done in January, the billing month would be March. [/FONT]
[FONT=&quot]My table has two fields. Date and DayOfWeek, Date being every day in the year in format m/d/yyyy and DayOfWeek being Sun, Mon, Tue, etc. [/FONT]
[FONT=&quot]Or am I not understanding the code you gave me at all, that is, do I even need my table?[/FONT]
[FONT=&quot]Thanks again,[/FONT]

 

[Isskint]

Yes you are right to save it into a module, but do not name the module the same as the function. You do not need to change anything in the code. Once you have saved this function, you can use it any where you would use any of the Access functions.

As a control source for a textbox on a form or report:
= DayCountPerMonth(DateToCheck,DayOfWeekToCount)

In a query as a calculated field OR as a criteria
= DayCountPerMonth(DateToCheck,DayOfWeekToCount)

In VBA:
Variable = DayCountPerMonth(DateToCheck,DayOfWeekToCount)

All you need to do is replace the 2 generic arguments:
DateToCheck with a valid date of the month you want to count the days in. The date can be supplied as a variable (MyVar = SomeDate), field name (Me.DateField or Forms!FormName!DateField), or literal #01/01/12#
DayOfWeekToCount with an integer 1 to 7 to indicate which day you want counted (1 = Sunday ... 7 = Saturday)



EDIT::

How embarassing, i made a mistake in the code

Correct code:

Function DayCountPerMonth(SourceDate As Date, DayOfWeekToReturn As Integer) As Integer
Dim iFirstDayInMonth, iLastDayInMonth, lp As Integer
Dim DaysInMonth(6) As Integer
iFirstDayInMonth = Weekday(DateSerial(Year(SourceDate), Month(SourceDate), 1)) 'get weekday for first of month
iLastDayInMonth = Day(DateSerial(Year(SourceDate), Month(SourceDate) + 1, 0)) 'get day for last day of month
For lp = 0 To 6
    DaysInMonth(lp) = 4
Next lp
Select Case iLastDayInMonth
    Case 29
        DaysInMonth(iFirstDayInMonth - 1) = 5
    Case 30
        DaysInMonth(iFirstDayInMonth - 1) = 5
        If iFirstDayInMonth = 7 Then
            DaysInMonth(0) = 5
        Else
            DaysInMonth(iFirstDayInMonth) = 5
        End If
    Case 31
        DaysInMonth(iFirstDayInMonth - 1) = 5
        If iFirstDayInMonth = 7 Then
            DaysInMonth(0) = 5
            DaysInMonth(1) = 5
        ElseIf iFirstDayInMonth = 6 Then
            DaysInMonth(6) = 5
            DaysInMonth(0) = 5
        Else
            DaysInMonth(iFirstDayInMonth) = 5
            DaysInMonth(iFirstDayInMonth + 1) = 5
        End If
End Select
DayCountPerMonth = DaysInMonth(DayOfWeekToReturn - 1)
End Function

I have also attached a DB that shows how the function can be used in a form and a query

 

[Eljefegeneo]

Thank you. When I show this to my office manager, she will really be impressed with me - but I will not take the credit. It works great and is just what I needed. Thanks again.