If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Can you count the number of Mondays in a month
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 |
#3
|
|||
|
|||
Can you count the number of Mondays in a month
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 |
#4
|
|||
|
|||
Can you count the number of Mondays in a month
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 |
#5
|
|||
|
|||
Can you count the number of Mondays in a month
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 |
#6
|
|||
|
|||
Can you count the number of Mondays in a month
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 |
#7
|
|||
|
|||
Can you count the number of Mondays in a month
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 |
#8
|
|||
|
|||
Can you count the number of Mondays in a month
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 |
Thread Tools | |
Display Modes | |
|
|