Can you count the number of Mondays in a month

[Box666]

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

 

[Douglas J. Steele]

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)

 

[Box666]

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

 

[Douglas J. Steele]

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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":

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)

 

[John Spencer]

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:

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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":

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)

--
Doug Steele, Microsoft Access MVP

(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
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

 

[Douglas J. Steele]

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

--
Doug Steele, Microsoft Access MVP

(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
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:

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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





Douglas J. Steele wrote:
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)

--
Doug Steele, Microsoft Access MVP

(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
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

 

[Box666]

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:

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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":

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)

--
Doug Steele, Microsoft Access MVP

(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
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

 

[Douglas J. Steele]

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)

--
Doug Steele, Microsoft Access MVP

(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

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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":

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)

--
Doug Steele, Microsoft Access MVP

(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
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