Can you count the number of Mondays in a month

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Box666 - 30 Nov 2006 12:49 GMT

I have a series of reports that are sent at at various times of the
month. Each report has to be sent out either by a set date or in some
case on a set day of the week. These details are recorded in two tables
(Report details and Reports sent)
At the end of each month I extract details of the reports sent out this
year to date, to see if they have all met the deadlines. My issue is
with reports sent out once a week say every Monday.
If a report is sent on a Monday it is logged as "On time" if it is sent
Tue - Friday it is logged as "late".But if it is never sent at all then
of course it is never recorded, so never shows in the end of month
figures.
I need to be able to count the number of Mondays in a month so I know
that the "on time" plus the "late" equal the number of Mondays in the
month.
So is there a way to count the number of (say) Mondays in a month. ..
or should I be looking at a different way to solve this problem.

With thanks

Reply to this Message

Douglas J. Steele - 30 Nov 2006 13:11 GMT

Take a look at http://www.mvps.org/access/datetime/date0011.htm at "The
Access Web":

HowManyWD(StartDate, EndDate, vbMonday)

If you want to know for the current month, you could use:

HowManyWD(DateSerial(Year(Date()), Month(Date()), 1), _
DateSerial(Year(Date()), Month(Date()) + 1, 0), _
vbMonday)

To get the first and last days of last month, you could use

DateSerial(Year(DateAdd("m", -1, Date())), Month(DateAdd("m", -1, Date())),
1)

and

DateSerial(Year(DateAdd("m", -1, Date())), Month(DateAdd("m", -1, Date())) +
1, 0)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I have a series of reports that are sent at at various times of the
> month. Each report has to be sent out either by a set date or in some
[quoted text clipped - 14 lines]
>
> With thanks

Reply to this Message

Box666 - 30 Nov 2006 15:28 GMT

Doug,
I have tried as suggested and keep getting a #NAME? error. Just to
confirm my actions :-

1. I have copied the code from mvps,
2. Pasted it into a module
3. Removed the "_" so code is all on one line
4. It looks ok, colour code seems fine no "red"

then on a blank form i have placedd an unbound field into which I have
copied and pasted your suggested

=HowManyWD(DateSerial(Year(Date()), Month(Date()), 1),
DateSerial(Year(Date()), Month(Date()) + 1, 0), vbMonday)

as well as the above I have also tried

=HowManyWD(#01/01/2006#;#31/01/2006#;[vbMonday])

I have tried it with and without the # and = it ends up putting the
[] around the day.

Any suggestions where I am going wrong. I am guessing it cannot find
the module ?

With thanks

> Take a look at http://www.mvps.org/access/datetime/date0011.htm at "The
> Access Web":
[quoted text clipped - 40 lines]
> >
> > With thanks

Reply to this Message

Douglas J. Steele - 30 Nov 2006 15:43 GMT

Not sure why it doesn't work as a ControlSource, but use the following as
the form's Load event:

Private Sub Form_Load()

Me.MyTextBox =
HowManyWD(DateSerial(Year(Date()),Month(Date()),1),DateSerial(Year(Date()),Month(Date())+1,0),vbmonday)

End Sub

(replace MyTextBox with the name of your actual text box.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Doug,
> I have tried as suggested and keep getting a #NAME? error. Just to
[quoted text clipped - 69 lines]
>> >
>> > With thanks

Reply to this Message

John Spencer - 30 Nov 2006 16:37 GMT

If you are using it as a control source or in a query try replacing vbMonday
with its value of 2. I believe that the vb constants are not available in
those situations.

HowManyWD(DateSerial(Year(Date()),Month(Date()),1),DateSerial(Year(Date()),Month(Date())+1,0),2)

> Not sure why it doesn't work as a ControlSource, but use the following as
> the form's Load event:
[quoted text clipped - 85 lines]
>>> >
>>> > With thanks

Reply to this Message

Douglas J. Steele - 30 Nov 2006 16:57 GMT

Thanks, John. You are, of course, correct.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> If you are using it as a control source or in a query try replacing
> vbMonday with its value of 2. I believe that the vb constants are not
[quoted text clipped - 91 lines]
>>>> >
>>>> > With thanks

Reply to this Message

Box666 - 30 Nov 2006 17:01 GMT

Thank you it works perfectly from the forms load Event.

I really wanted to use it in a series of queries (I was only testing it
"quickly" to be sure it worked ok) and as soon as I try and enter it in
a query as

MonDysInMth:HowManyWD(DateSerial(Year(Date()),Month(Date()),1),DateSerial(Year(Date()),?Month(Date())+1,0),vbmonday)

then I come back to the same problem, in that it puts [ ] around
vbmonday, and then when it runs, it throws up "Enter Peramiter Value"
for vbmonday.

So I believe it is not finding the module into which the original code
was loaded. Does any obvious error in this regard spring to mind?

Bob

> Not sure why it doesn't work as a ControlSource, but use the following as
> the form's Load event:
[quoted text clipped - 86 lines]
> >> >
> >> > With thanks

Reply to this Message

Douglas J. Steele - 30 Nov 2006 17:16 GMT

You can only use the intrinsic constants in VBA. In a query, you have to
supply the value of vbMonday (which, as John Spencer pointed out
else-thread, is 2)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Thank you it works perfectly from the forms load Event.

I really wanted to use it in a series of queries (I was only testing it
"quickly" to be sure it worked ok) and as soon as I try and enter it in
a query as

MonDysInMth:HowManyWD(DateSerial(Year(Date()),Month(Date()),1),DateSerial(Year(Date()),Month(Date())+1,0),vbmonday)

then I come back to the same problem, in that it puts [ ] around
vbmonday, and then when it runs, it throws up "Enter Peramiter Value"
for vbmonday.

So I believe it is not finding the module into which the original code
was loaded. Does any obvious error in this regard spring to mind?

Bob

Douglas J. Steele wrote:

> Not sure why it doesn't work as a ControlSource, but use the following as
> the form's Load event:
[quoted text clipped - 91 lines]
> >> >
> >> > With thanks

Reply to this Message