J
Jason
How do I write a function that will give me the number of Mondays that occur
in a given month?
in a given month?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
K
Klatuu
Here you go:
'---------------------------------------------------------------------------------------
' Procedure : CountWeekDays
' DateTime : 4/10/2008 10:36
' Author : Dave Hargis
' Purpose : Returns the number of occurances of a specific weekday in a
given month
' : and year
' : Arguments
' : lngYear The year to be used in the calculations
' : lngMonth The month to be used in the calculations
' : lngWeekDay The Day of the week Where Sunday = 1 and
Saturday = 7
' : Returns A Long Interger representing the number of times
the
' : specified weekday occurs for in the year/month
'---------------------------------------------------------------------------------------
'
Public Function CountWeekDays(lngYear As Long, lngMonth As Long, lngWeekday
As Long) As Long
Dim dtmDate As Date
On Error GoTo CountWeekDays_Error
dtmDate = DateSerial(lngYear, lngMonth, 1)
Do While Month(dtmDate) = lngMonth
If Weekday(dtmDate) = lngWeekday Then
CountWeekDays = CountWeekDays + 1
End If
dtmDate = DateAdd("d", 1, dtmDate)
Loop
CountWeekDays_Exit:
On Error GoTo 0
Exit Function
CountWeekDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CountWeekDays of Module modDateFunctions"
GoTo CountWeekDays_Exit
End Function
'---------------------------------------------------------------------------------------
' Procedure : CountWeekDays
' DateTime : 4/10/2008 10:36
' Author : Dave Hargis
' Purpose : Returns the number of occurances of a specific weekday in a
given month
' : and year
' : Arguments
' : lngYear The year to be used in the calculations
' : lngMonth The month to be used in the calculations
' : lngWeekDay The Day of the week Where Sunday = 1 and
Saturday = 7
' : Returns A Long Interger representing the number of times
the
' : specified weekday occurs for in the year/month
'---------------------------------------------------------------------------------------
'
Public Function CountWeekDays(lngYear As Long, lngMonth As Long, lngWeekday
As Long) As Long
Dim dtmDate As Date
On Error GoTo CountWeekDays_Error
dtmDate = DateSerial(lngYear, lngMonth, 1)
Do While Month(dtmDate) = lngMonth
If Weekday(dtmDate) = lngWeekday Then
CountWeekDays = CountWeekDays + 1
End If
dtmDate = DateAdd("d", 1, dtmDate)
Loop
CountWeekDays_Exit:
On Error GoTo 0
Exit Function
CountWeekDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CountWeekDays of Module modDateFunctions"
GoTo CountWeekDays_Exit
End Function
J
Jason
Thanks Klatuu - now I need a little help using your function.
I've copied to code below to a module. Then I've tried to use the function
in my query, but I keep getting an error, "Wrong number of arguments used
with function in query expression 'countweekdays(...)'.
I've tried several different ways to pass a date to the function without
success.
What I want to end up with in the query below is the # of Mondays in the
month for the "Date of Observation" field. The SQL for the query (a totals
query) is shown below.
Thanks for the function and your help showing me how to use it!
SELECT qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation]) AS [Month],
Count(BOS1.[Date of Observation]) AS [CountOfDate of Observation],
countweekdays(year([Date of Observation],month([Date of Observation]),2) AS
[Number Rqrd], IIf(([CountOfDate of Observation]/[Number
Rqrd])>1,1,[CountOfDate of Observation]/[Number Rqrd]) AS [BOS Participation]
FROM qryNames LEFT JOIN BOS1 ON (qryNames.[Last Name] = BOS1.[Last Name])
AND (qryNames.[First Name] = BOS1.[First Name])
WHERE (((Month([Date of Observation]) & "/" & Year([date of
observation]))=[Enter Month]))
GROUP BY qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation])
HAVING ((Not (qryNames.[First Name]) Is Null));
I've copied to code below to a module. Then I've tried to use the function
in my query, but I keep getting an error, "Wrong number of arguments used
with function in query expression 'countweekdays(...)'.
I've tried several different ways to pass a date to the function without
success.
What I want to end up with in the query below is the # of Mondays in the
month for the "Date of Observation" field. The SQL for the query (a totals
query) is shown below.
Thanks for the function and your help showing me how to use it!
SELECT qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation]) AS [Month],
Count(BOS1.[Date of Observation]) AS [CountOfDate of Observation],
countweekdays(year([Date of Observation],month([Date of Observation]),2) AS
[Number Rqrd], IIf(([CountOfDate of Observation]/[Number
Rqrd])>1,1,[CountOfDate of Observation]/[Number Rqrd]) AS [BOS Participation]
FROM qryNames LEFT JOIN BOS1 ON (qryNames.[Last Name] = BOS1.[Last Name])
AND (qryNames.[First Name] = BOS1.[First Name])
WHERE (((Month([Date of Observation]) & "/" & Year([date of
observation]))=[Enter Month]))
GROUP BY qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation])
HAVING ((Not (qryNames.[First Name]) Is Null));
K
Klatuu
You are missin a paren v
countweekdays(year([Date of Observation],month([Date of Observation]),2)
Should be
countweekdays(year([Date of Observation]),month([Date of Observation]),2)
Also, if there is any chance [Date of Observation] could be null, I would
suggest trapping for it:
IIf IsNull([Date of Observation], 0, countweekdays(year([Date of
Observation],month([Date of Observation]),2))
--
Dave Hargis, Microsoft Access MVP
countweekdays(year([Date of Observation],month([Date of Observation]),2)
Should be
countweekdays(year([Date of Observation]),month([Date of Observation]),2)
Also, if there is any chance [Date of Observation] could be null, I would
suggest trapping for it:
IIf IsNull([Date of Observation], 0, countweekdays(year([Date of
Observation],month([Date of Observation]),2))
--
Dave Hargis, Microsoft Access MVP
Jason said:Thanks Klatuu - now I need a little help using your function.
I've copied to code below to a module. Then I've tried to use the function
in my query, but I keep getting an error, "Wrong number of arguments used
with function in query expression 'countweekdays(...)'.
I've tried several different ways to pass a date to the function without
success.
What I want to end up with in the query below is the # of Mondays in the
month for the "Date of Observation" field. The SQL for the query (a totals
query) is shown below.
Thanks for the function and your help showing me how to use it!
SELECT qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation]) AS [Month],
Count(BOS1.[Date of Observation]) AS [CountOfDate of Observation],
countweekdays(year([Date of Observation],month([Date of Observation]),2) AS
[Number Rqrd], IIf(([CountOfDate of Observation]/[Number
Rqrd])>1,1,[CountOfDate of Observation]/[Number Rqrd]) AS [BOS Participation]
FROM qryNames LEFT JOIN BOS1 ON (qryNames.[Last Name] = BOS1.[Last Name])
AND (qryNames.[First Name] = BOS1.[First Name])
WHERE (((Month([Date of Observation]) & "/" & Year([date of
observation]))=[Enter Month]))
GROUP BY qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation])
HAVING ((Not (qryNames.[First Name]) Is Null));
--
Thanks,
Jason
Klatuu said:Here you go:
'---------------------------------------------------------------------------------------
' Procedure : CountWeekDays
' DateTime : 4/10/2008 10:36
' Author : Dave Hargis
' Purpose : Returns the number of occurances of a specific weekday in a
given month
' : and year
' : Arguments
' : lngYear The year to be used in the calculations
' : lngMonth The month to be used in the calculations
' : lngWeekDay The Day of the week Where Sunday = 1 and
Saturday = 7
' : Returns A Long Interger representing the number of times
the
' : specified weekday occurs for in the year/month
'---------------------------------------------------------------------------------------
'
Public Function CountWeekDays(lngYear As Long, lngMonth As Long, lngWeekday
As Long) As Long
Dim dtmDate As Date
On Error GoTo CountWeekDays_Error
dtmDate = DateSerial(lngYear, lngMonth, 1)
Do While Month(dtmDate) = lngMonth
If Weekday(dtmDate) = lngWeekday Then
CountWeekDays = CountWeekDays + 1
End If
dtmDate = DateAdd("d", 1, dtmDate)
Loop
CountWeekDays_Exit:
On Error GoTo 0
Exit Function
CountWeekDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CountWeekDays of Module modDateFunctions"
GoTo CountWeekDays_Exit
End Function
J
Jason
Thanks Dave - I'm still missing something though.
I know it has to be a simple mistake. I've copied both the SQL for the query
and the module code below. Now I am getting the error shown below. I maybe
should also mention that I'm using Access 2000?
Syntax error (missing operator) in query expression
'countweekdays(year([Date of Observation]),month([Date of Observation]),2) AS
[Number Rqrd], IIf(([CountOfDate of Observation]/[Number
Rqrd])>1,1,[CountOfDate of Observation]/[Number Rqrd]) AS [BOS Participation]
FROM qryNames LEFT JOIN BOS1 On qryNames.['.
The SQL
SELECT qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation]) AS [Month],
Count(BOS1.[Date of Observation]) AS [CountOfDate of Observation],
countweekdays(year([Date of Observation],month([Date of Observation]),2) AS
[Number Rqrd], IIf(([CountOfDate of Observation]/[Number
Rqrd])>1,1,[CountOfDate of Observation]/[Number Rqrd]) AS [BOS Participation]
FROM qryNames LEFT JOIN BOS1 ON (qryNames.[Last Name] = BOS1.[Last Name])
AND (qryNames.[First Name] = BOS1.[First Name])
WHERE (((Month([Date of Observation]) & "/" & Year([date of
observation]))=[Enter Month]))
GROUP BY qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation])
HAVING ((Not (qryNames.[First Name]) Is Null));
The Module code (Module 1)
Option Compare Database
'---------------------------------------------------------------------------------------
' Procedure : CountWeekDays
' DateTime : 4/10/2008 10:36
' Author : Dave Hargis
' Purpose : Returns the number of occurances of a specific weekday in a
given Month
' : and year
' : Arguments
' : lngYear The year to be used in the calculations
' : lngMonth The month to be used in the calculations
' : lngWeekDay The Day of the week Where Sunday = 1 and
Saturday = 7
' : Returns A Long Interger representing the number of times
the
' : specified weekday occurs for in the year/mont
'---------------------------------------------------------------------------------------
'
Public Function CountWeekDays(lngYear As Long, lngMonth As Long, lngWeekday
As Long) As Long
Dim dtmDate As Date
On Error GoTo CountWeekDays_Error
dtmDate = DateSerial(lngYear, lngMonth, 1)
Do While Month(dtmDate) = lngMonth
If Weekday(dtmDate) = lngWeekday Then
CountWeekDays = CountWeekDays + 1
End If
dtmDate = DateAdd("d", 1, dtmDate)
Loop
CountWeekDays_Exit:
On Error GoTo 0
Exit Function
CountWeekDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CountWeekDays of Module modDateFunctions"
GoTo CountWeekDays_Exit
End Function
--
Thanks,
Jason
I know it has to be a simple mistake. I've copied both the SQL for the query
and the module code below. Now I am getting the error shown below. I maybe
should also mention that I'm using Access 2000?
Syntax error (missing operator) in query expression
'countweekdays(year([Date of Observation]),month([Date of Observation]),2) AS
[Number Rqrd], IIf(([CountOfDate of Observation]/[Number
Rqrd])>1,1,[CountOfDate of Observation]/[Number Rqrd]) AS [BOS Participation]
FROM qryNames LEFT JOIN BOS1 On qryNames.['.
The SQL
SELECT qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation]) AS [Month],
Count(BOS1.[Date of Observation]) AS [CountOfDate of Observation],
countweekdays(year([Date of Observation],month([Date of Observation]),2) AS
[Number Rqrd], IIf(([CountOfDate of Observation]/[Number
Rqrd])>1,1,[CountOfDate of Observation]/[Number Rqrd]) AS [BOS Participation]
FROM qryNames LEFT JOIN BOS1 ON (qryNames.[Last Name] = BOS1.[Last Name])
AND (qryNames.[First Name] = BOS1.[First Name])
WHERE (((Month([Date of Observation]) & "/" & Year([date of
observation]))=[Enter Month]))
GROUP BY qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation])
HAVING ((Not (qryNames.[First Name]) Is Null));
The Module code (Module 1)
Option Compare Database
'---------------------------------------------------------------------------------------
' Procedure : CountWeekDays
' DateTime : 4/10/2008 10:36
' Author : Dave Hargis
' Purpose : Returns the number of occurances of a specific weekday in a
given Month
' : and year
' : Arguments
' : lngYear The year to be used in the calculations
' : lngMonth The month to be used in the calculations
' : lngWeekDay The Day of the week Where Sunday = 1 and
Saturday = 7
' : Returns A Long Interger representing the number of times
the
' : specified weekday occurs for in the year/mont
'---------------------------------------------------------------------------------------
'
Public Function CountWeekDays(lngYear As Long, lngMonth As Long, lngWeekday
As Long) As Long
Dim dtmDate As Date
On Error GoTo CountWeekDays_Error
dtmDate = DateSerial(lngYear, lngMonth, 1)
Do While Month(dtmDate) = lngMonth
If Weekday(dtmDate) = lngWeekday Then
CountWeekDays = CountWeekDays + 1
End If
dtmDate = DateAdd("d", 1, dtmDate)
Loop
CountWeekDays_Exit:
On Error GoTo 0
Exit Function
CountWeekDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CountWeekDays of Module modDateFunctions"
GoTo CountWeekDays_Exit
End Function
--
Thanks,
Jason
Klatuu said:You are missin a paren v
countweekdays(year([Date of Observation],month([Date of Observation]),2)
Should be
countweekdays(year([Date of Observation]),month([Date of Observation]),2)
Also, if there is any chance [Date of Observation] could be null, I would
suggest trapping for it:
IIf IsNull([Date of Observation], 0, countweekdays(year([Date of
Observation],month([Date of Observation]),2))
--
Dave Hargis, Microsoft Access MVP
Jason said:Thanks Klatuu - now I need a little help using your function.
I've copied to code below to a module. Then I've tried to use the function
in my query, but I keep getting an error, "Wrong number of arguments used
with function in query expression 'countweekdays(...)'.
I've tried several different ways to pass a date to the function without
success.
What I want to end up with in the query below is the # of Mondays in the
month for the "Date of Observation" field. The SQL for the query (a totals
query) is shown below.
Thanks for the function and your help showing me how to use it!
SELECT qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation]) AS [Month],
Count(BOS1.[Date of Observation]) AS [CountOfDate of Observation],
countweekdays(year([Date of Observation],month([Date of Observation]),2) AS
[Number Rqrd], IIf(([CountOfDate of Observation]/[Number
Rqrd])>1,1,[CountOfDate of Observation]/[Number Rqrd]) AS [BOS Participation]
FROM qryNames LEFT JOIN BOS1 ON (qryNames.[Last Name] = BOS1.[Last Name])
AND (qryNames.[First Name] = BOS1.[First Name])
WHERE (((Month([Date of Observation]) & "/" & Year([date of
observation]))=[Enter Month]))
GROUP BY qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation])
HAVING ((Not (qryNames.[First Name]) Is Null));
--
Thanks,
Jason
Klatuu said:Here you go:
'---------------------------------------------------------------------------------------
' Procedure : CountWeekDays
' DateTime : 4/10/2008 10:36
' Author : Dave Hargis
' Purpose : Returns the number of occurances of a specific weekday in a
given month
' : and year
' : Arguments
' : lngYear The year to be used in the calculations
' : lngMonth The month to be used in the calculations
' : lngWeekDay The Day of the week Where Sunday = 1 and
Saturday = 7
' : Returns A Long Interger representing the number of times
the
' : specified weekday occurs for in the year/month
'---------------------------------------------------------------------------------------
'
Public Function CountWeekDays(lngYear As Long, lngMonth As Long, lngWeekday
As Long) As Long
Dim dtmDate As Date
On Error GoTo CountWeekDays_Error
dtmDate = DateSerial(lngYear, lngMonth, 1)
Do While Month(dtmDate) = lngMonth
If Weekday(dtmDate) = lngWeekday Then
CountWeekDays = CountWeekDays + 1
End If
dtmDate = DateAdd("d", 1, dtmDate)
Loop
CountWeekDays_Exit:
On Error GoTo 0
Exit Function
CountWeekDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CountWeekDays of Module modDateFunctions"
GoTo CountWeekDays_Exit
End Function
--
Dave Hargis, Microsoft Access MVP
:
How do I write a function that will give me the number of Mondays that occur
in a given month?
K
Klatuu
Nothing jumps out at me, but I am not sure you can reference a calculated
field in the same query where it is calculated. Try it this way:
'countweekdays(year([Date of Observation]),month([Date of Observation]),2) AS
[Number Rqrd], IIf(([CountOfDate of Observation]/[Number
Rqrd])>1,1,[CountOfDate of Observation]/countweekdays(year([Date of
Observation]),month([Date of Observation]),2)) AS [BOS Participation]
FROM qryNames LEFT JOIN BOS1 On qryNames.['.
Basically, you would be running the function two times for each row. There
would be nothing in the function that would cause that or it would error on
in the code, not in the query.
Let me know if that works.
--
Dave Hargis, Microsoft Access MVP
field in the same query where it is calculated. Try it this way:
'countweekdays(year([Date of Observation]),month([Date of Observation]),2) AS
[Number Rqrd], IIf(([CountOfDate of Observation]/[Number
Rqrd])>1,1,[CountOfDate of Observation]/countweekdays(year([Date of
Observation]),month([Date of Observation]),2)) AS [BOS Participation]
FROM qryNames LEFT JOIN BOS1 On qryNames.['.
Basically, you would be running the function two times for each row. There
would be nothing in the function that would cause that or it would error on
in the code, not in the query.
Let me know if that works.
--
Dave Hargis, Microsoft Access MVP
Jason said:Thanks Dave - I'm still missing something though.
I know it has to be a simple mistake. I've copied both the SQL for the query
and the module code below. Now I am getting the error shown below. I maybe
should also mention that I'm using Access 2000?
Syntax error (missing operator) in query expression
'countweekdays(year([Date of Observation]),month([Date of Observation]),2) AS
[Number Rqrd], IIf(([CountOfDate of Observation]/[Number
Rqrd])>1,1,[CountOfDate of Observation]/[Number Rqrd]) AS [BOS Participation]
FROM qryNames LEFT JOIN BOS1 On qryNames.['.
The SQL
SELECT qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation]) AS [Month],
Count(BOS1.[Date of Observation]) AS [CountOfDate of Observation],
countweekdays(year([Date of Observation],month([Date of Observation]),2) AS
[Number Rqrd], IIf(([CountOfDate of Observation]/[Number
Rqrd])>1,1,[CountOfDate of Observation]/[Number Rqrd]) AS [BOS Participation]
FROM qryNames LEFT JOIN BOS1 ON (qryNames.[Last Name] = BOS1.[Last Name])
AND (qryNames.[First Name] = BOS1.[First Name])
WHERE (((Month([Date of Observation]) & "/" & Year([date of
observation]))=[Enter Month]))
GROUP BY qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation])
HAVING ((Not (qryNames.[First Name]) Is Null));
The Module code (Module 1)
Option Compare Database
'---------------------------------------------------------------------------------------
' Procedure : CountWeekDays
' DateTime : 4/10/2008 10:36
' Author : Dave Hargis
' Purpose : Returns the number of occurances of a specific weekday in a
given Month
' : and year
' : Arguments
' : lngYear The year to be used in the calculations
' : lngMonth The month to be used in the calculations
' : lngWeekDay The Day of the week Where Sunday = 1 and
Saturday = 7
' : Returns A Long Interger representing the number of times
the
' : specified weekday occurs for in the year/month
'---------------------------------------------------------------------------------------
'
Public Function CountWeekDays(lngYear As Long, lngMonth As Long, lngWeekday
As Long) As Long
Dim dtmDate As Date
On Error GoTo CountWeekDays_Error
dtmDate = DateSerial(lngYear, lngMonth, 1)
Do While Month(dtmDate) = lngMonth
If Weekday(dtmDate) = lngWeekday Then
CountWeekDays = CountWeekDays + 1
End If
dtmDate = DateAdd("d", 1, dtmDate)
Loop
CountWeekDays_Exit:
On Error GoTo 0
Exit Function
CountWeekDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CountWeekDays of Module modDateFunctions"
GoTo CountWeekDays_Exit
End Function
--
Thanks,
Jason
Klatuu said:You are missin a paren v
countweekdays(year([Date of Observation],month([Date of Observation]),2)
Should be
countweekdays(year([Date of Observation]),month([Date of Observation]),2)
Also, if there is any chance [Date of Observation] could be null, I would
suggest trapping for it:
IIf IsNull([Date of Observation], 0, countweekdays(year([Date of
Observation],month([Date of Observation]),2))
--
Dave Hargis, Microsoft Access MVP
Jason said:Thanks Klatuu - now I need a little help using your function.
I've copied to code below to a module. Then I've tried to use the function
in my query, but I keep getting an error, "Wrong number of arguments used
with function in query expression 'countweekdays(...)'.
I've tried several different ways to pass a date to the function without
success.
What I want to end up with in the query below is the # of Mondays in the
month for the "Date of Observation" field. The SQL for the query (a totals
query) is shown below.
Thanks for the function and your help showing me how to use it!
SELECT qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation]) AS [Month],
Count(BOS1.[Date of Observation]) AS [CountOfDate of Observation],
countweekdays(year([Date of Observation],month([Date of Observation]),2) AS
[Number Rqrd], IIf(([CountOfDate of Observation]/[Number
Rqrd])>1,1,[CountOfDate of Observation]/[Number Rqrd]) AS [BOS Participation]
FROM qryNames LEFT JOIN BOS1 ON (qryNames.[Last Name] = BOS1.[Last Name])
AND (qryNames.[First Name] = BOS1.[First Name])
WHERE (((Month([Date of Observation]) & "/" & Year([date of
observation]))=[Enter Month]))
GROUP BY qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation])
HAVING ((Not (qryNames.[First Name]) Is Null));
--
Thanks,
Jason
:
Here you go:
'---------------------------------------------------------------------------------------
' Procedure : CountWeekDays
' DateTime : 4/10/2008 10:36
' Author : Dave Hargis
' Purpose : Returns the number of occurances of a specific weekday in a
given month
' : and year
' : Arguments
' : lngYear The year to be used in the calculations
' : lngMonth The month to be used in the calculations
' : lngWeekDay The Day of the week Where Sunday = 1 and
Saturday = 7
' : Returns A Long Interger representing the number of times
the
' : specified weekday occurs for in the year/month
'---------------------------------------------------------------------------------------
'
Public Function CountWeekDays(lngYear As Long, lngMonth As Long, lngWeekday
As Long) As Long
Dim dtmDate As Date
On Error GoTo CountWeekDays_Error
dtmDate = DateSerial(lngYear, lngMonth, 1)
Do While Month(dtmDate) = lngMonth
If Weekday(dtmDate) = lngWeekday Then
CountWeekDays = CountWeekDays + 1
End If
dtmDate = DateAdd("d", 1, dtmDate)
Loop
CountWeekDays_Exit:
On Error GoTo 0
Exit Function
CountWeekDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CountWeekDays of Module modDateFunctions"
GoTo CountWeekDays_Exit
End Function
--
Dave Hargis, Microsoft Access MVP
:
How do I write a function that will give me the number of Mondays that occur
in a given month?
K
Klatuu
Oh, I almost forgot.
This line:
Month([Date of Observation]) & "/" & Year([date of observation]) AS [Month],
Has two issues.
1. Don't name anything Month or Year or any other Access or SQL reserved
word. Enclosing it in brackets will usually resolve any confusion, but I
have seen times when it still has a problem with it.
And, this would be better form:
Format(([date of observation],"mm\/yyyy") As [ObservedMonth]
--
Dave Hargis, Microsoft Access MVP
This line:
Month([Date of Observation]) & "/" & Year([date of observation]) AS [Month],
Has two issues.
1. Don't name anything Month or Year or any other Access or SQL reserved
word. Enclosing it in brackets will usually resolve any confusion, but I
have seen times when it still has a problem with it.
And, this would be better form:
Format(([date of observation],"mm\/yyyy") As [ObservedMonth]
--
Dave Hargis, Microsoft Access MVP
Jason said:Thanks Dave - I'm still missing something though.
I know it has to be a simple mistake. I've copied both the SQL for the query
and the module code below. Now I am getting the error shown below. I maybe
should also mention that I'm using Access 2000?
Syntax error (missing operator) in query expression
'countweekdays(year([Date of Observation]),month([Date of Observation]),2) AS
[Number Rqrd], IIf(([CountOfDate of Observation]/[Number
Rqrd])>1,1,[CountOfDate of Observation]/[Number Rqrd]) AS [BOS Participation]
FROM qryNames LEFT JOIN BOS1 On qryNames.['.
The SQL
SELECT qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation]) AS [Month],
Count(BOS1.[Date of Observation]) AS [CountOfDate of Observation],
countweekdays(year([Date of Observation],month([Date of Observation]),2) AS
[Number Rqrd], IIf(([CountOfDate of Observation]/[Number
Rqrd])>1,1,[CountOfDate of Observation]/[Number Rqrd]) AS [BOS Participation]
FROM qryNames LEFT JOIN BOS1 ON (qryNames.[Last Name] = BOS1.[Last Name])
AND (qryNames.[First Name] = BOS1.[First Name])
WHERE (((Month([Date of Observation]) & "/" & Year([date of
observation]))=[Enter Month]))
GROUP BY qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation])
HAVING ((Not (qryNames.[First Name]) Is Null));
The Module code (Module 1)
Option Compare Database
'---------------------------------------------------------------------------------------
' Procedure : CountWeekDays
' DateTime : 4/10/2008 10:36
' Author : Dave Hargis
' Purpose : Returns the number of occurances of a specific weekday in a
given Month
' : and year
' : Arguments
' : lngYear The year to be used in the calculations
' : lngMonth The month to be used in the calculations
' : lngWeekDay The Day of the week Where Sunday = 1 and
Saturday = 7
' : Returns A Long Interger representing the number of times
the
' : specified weekday occurs for in the year/month
'---------------------------------------------------------------------------------------
'
Public Function CountWeekDays(lngYear As Long, lngMonth As Long, lngWeekday
As Long) As Long
Dim dtmDate As Date
On Error GoTo CountWeekDays_Error
dtmDate = DateSerial(lngYear, lngMonth, 1)
Do While Month(dtmDate) = lngMonth
If Weekday(dtmDate) = lngWeekday Then
CountWeekDays = CountWeekDays + 1
End If
dtmDate = DateAdd("d", 1, dtmDate)
Loop
CountWeekDays_Exit:
On Error GoTo 0
Exit Function
CountWeekDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CountWeekDays of Module modDateFunctions"
GoTo CountWeekDays_Exit
End Function
--
Thanks,
Jason
Klatuu said:You are missin a paren v
countweekdays(year([Date of Observation],month([Date of Observation]),2)
Should be
countweekdays(year([Date of Observation]),month([Date of Observation]),2)
Also, if there is any chance [Date of Observation] could be null, I would
suggest trapping for it:
IIf IsNull([Date of Observation], 0, countweekdays(year([Date of
Observation],month([Date of Observation]),2))
--
Dave Hargis, Microsoft Access MVP
Jason said:Thanks Klatuu - now I need a little help using your function.
I've copied to code below to a module. Then I've tried to use the function
in my query, but I keep getting an error, "Wrong number of arguments used
with function in query expression 'countweekdays(...)'.
I've tried several different ways to pass a date to the function without
success.
What I want to end up with in the query below is the # of Mondays in the
month for the "Date of Observation" field. The SQL for the query (a totals
query) is shown below.
Thanks for the function and your help showing me how to use it!
SELECT qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation]) AS [Month],
Count(BOS1.[Date of Observation]) AS [CountOfDate of Observation],
countweekdays(year([Date of Observation],month([Date of Observation]),2) AS
[Number Rqrd], IIf(([CountOfDate of Observation]/[Number
Rqrd])>1,1,[CountOfDate of Observation]/[Number Rqrd]) AS [BOS Participation]
FROM qryNames LEFT JOIN BOS1 ON (qryNames.[Last Name] = BOS1.[Last Name])
AND (qryNames.[First Name] = BOS1.[First Name])
WHERE (((Month([Date of Observation]) & "/" & Year([date of
observation]))=[Enter Month]))
GROUP BY qryNames.[First Name], qryNames.[Last Name], Month([Date of
Observation]) & "/" & Year([date of observation])
HAVING ((Not (qryNames.[First Name]) Is Null));
--
Thanks,
Jason
:
Here you go:
'---------------------------------------------------------------------------------------
' Procedure : CountWeekDays
' DateTime : 4/10/2008 10:36
' Author : Dave Hargis
' Purpose : Returns the number of occurances of a specific weekday in a
given month
' : and year
' : Arguments
' : lngYear The year to be used in the calculations
' : lngMonth The month to be used in the calculations
' : lngWeekDay The Day of the week Where Sunday = 1 and
Saturday = 7
' : Returns A Long Interger representing the number of times
the
' : specified weekday occurs for in the year/month
'---------------------------------------------------------------------------------------
'
Public Function CountWeekDays(lngYear As Long, lngMonth As Long, lngWeekday
As Long) As Long
Dim dtmDate As Date
On Error GoTo CountWeekDays_Error
dtmDate = DateSerial(lngYear, lngMonth, 1)
Do While Month(dtmDate) = lngMonth
If Weekday(dtmDate) = lngWeekday Then
CountWeekDays = CountWeekDays + 1
End If
dtmDate = DateAdd("d", 1, dtmDate)
Loop
CountWeekDays_Exit:
On Error GoTo 0
Exit Function
CountWeekDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CountWeekDays of Module modDateFunctions"
GoTo CountWeekDays_Exit
End Function
--
Dave Hargis, Microsoft Access MVP
:
How do I write a function that will give me the number of Mondays that occur
in a given month?
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.