Can you count the number of Mondays in a month

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

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
http://I.Am/DougSteele
(no e-mails, please!)


"Box666" wrote in message
ups.com...
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

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
http://I.Am/DougSteele
(no e-mails, please!)


"Box666" wrote in message
ups.com...
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

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),vbmond ay)

End Sub

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

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


"Box666" wrote in message
oups.com...
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
http://I.Am/DougSteele
(no e-mails, please!)


"Box666" wrote in message
ups.com...
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

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)


"Douglas J. Steele" wrote in message
...
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),vbmond ay)

End Sub

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

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


"Box666" wrote in message
oups.com...
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
http://I.Am/DougSteele
(no e-mails, please!)


"Box666" wrote in message
ups.com...
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

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

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


"John Spencer" wrote in message
...
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)


"Douglas J. Steele" wrote in message
...
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),vbmond ay)

End Sub

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

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


"Box666" wrote in message
oups.com...
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
http://I.Am/DougSteele
(no e-mails, please!)


"Box666" wrote in message
ups.com...
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

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()),Mont h(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:

Private Sub Form_Load()

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

End Sub

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

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


"Box666" wrote in message
oups.com...
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
http://I.Am/DougSteele
(no e-mails, please!)


"Box666" wrote in message
ups.com...
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

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
http://I.Am/DougSteele
(no e-mails, please!)


"Box666" wrote in message
ups.com...
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()),Mont h(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:

Private Sub Form_Load()

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

End Sub

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

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


"Box666" wrote in message
oups.com...
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
http://I.Am/DougSteele
(no e-mails, please!)


"Box666" wrote in message
ups.com...
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