By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,517 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

Counting specific Week days

P: n/a
I have tried using the following code to count the specific number of
each weekday but get a compile error "User defined type not defined"
which I think relates to the first line of the function: -

Public Function HowManySpecificDays(StartDate As Date, EndDate As
Date) As DayCounts

and specifically the word "DayCounts"

Can anyone help?

Full code is as follows.

Private Sub CalcDays_Click()

Call HowManySpecificDays("01/01/2002", "31/12/2002")

Debug.Print "Between these 2 dates there are:"
Debug.Print Count.Mon & " Mondays"
Debug.Print Count.Tue & " Tuesdays"
Debug.Print Count.Wed & " Wednesdays"
Debug.Print Count.Thu & " Thursdays"
Debug.Print Count.Fri & " Fridays"
Debug.Print Count.Mon + Count.Tue + Count.Wed + Count.Thu +
Count.Fri & " Chargeable Days"
Debug.Print Count.TotDays & " Total Days"
End Sub
Public Function HowManySpecificDays(StartDate As Date, EndDate As
Date) As DayCounts

On Error GoTo HowManySpecificDays_Error

Dim dteDateStart As Date
Dim dteDateEnd As Date

Dim intMondays As Integer
Dim intTuesdays As Integer
Dim intWednesdays As Integer
Dim intThursdays As Integer
Dim intFridays As Integer
Dim intTotDays As Integer

Dim intDay As Integer
Dim intMonth As Integer
Dim intMonthCount As Integer

dteDateStart = Format(StartDate, "dd/mm/yyyy")

dteDateEnd = Format(EndDate, "dd/mm/yyyy")

intMonthCount = 1
intMonth = Month(dteDateStart)

'Loop through the date range and work out how many days in the
period
Do Until dteDateStart = dteDateEnd + 1

intDay = WeekDay(dteDateStart)

If Not intMonth = Month(dteDateStart) Then
intMonth = Month(dteDateStart)
intMonthCount = intMonthCount + 1
End If
Select Case intDay

Case 2 'Mondays
intMondays = intMondays + 1

Case 3 'Tuesdays
intTuesdays = intTuesdays + 1

Case 4 'Wednesdays
intWednesdays = intWednesdays + 1

Case 5 'Thursdays
intThursdays = intThursdays + 1

Case 6 'Fridays
intFridays = intFridays + 1

Case Else

End Select

dteDateStart = dteDateStart + 1
intTotDays = intTotDays + 1
Loop

With Count
.Mon = intMondays
.Tue = intTuesdays
.Wed = intWednesdays
.Thu = intThursdays
.Fri = intFridays
.Months = intMonthCount
.TotDays = intTotDays
End With

HowManySpecificDays = Count

On Error GoTo 0
Exit Function

HowManySpecificDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure HowManySpecificDays"

End Function
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

You haven't got a declaration for Count.

This will work
'************************************************* **
' Version 1
'************************************************* **
Private Type DayCounts
Mon As Long
Tue As Long
Wed As Long
Thu As Long
Fri As Long
Months As Long
TotDays As Long
End Type

Private Count As DayCounts

Private Sub CalcDays_Click()
Call HowManySpecificDays("01/01/2002", "31/12/2002")

Debug.Print "Between these 2 dates there are:"
Debug.Print Count.Mon & " Mondays"
Debug.Print Count.Tue & " Tuesdays"
Debug.Print Count.Wed & " Wednesdays"
Debug.Print Count.Thu & " Thursdays"
Debug.Print Count.Fri & " Fridays"
Debug.Print Count.Mon + Count.Tue + Count.Wed + Count.Thu +
Count.Fri & " Chargeable Days"
Debug.Print Count.TotDays & " Total Days"
End Sub
Public Function HowManySpecificDays(StartDate As Date, EndDate As Date) As
DayCounts

On Error GoTo HowManySpecificDays_Error

Dim dteDateStart As Date
Dim dteDateEnd As Date

Dim intMondays As Integer
Dim intTuesdays As Integer
Dim intWednesdays As Integer
Dim intThursdays As Integer
Dim intFridays As Integer
Dim intTotDays As Integer

Dim intDay As Integer
Dim intMonth As Integer
Dim intMonthCount As Integer

dteDateStart = Format(StartDate, "dd/mm/yyyy")

dteDateEnd = Format(EndDate, "dd/mm/yyyy")

intMonthCount = 1
intMonth = Month(dteDateStart)

'Loop through the date range and work out how many days in the period
Do Until dteDateStart = dteDateEnd + 1

intDay = Weekday(dteDateStart)

If Not intMonth = Month(dteDateStart) Then
intMonth = Month(dteDateStart)
intMonthCount = intMonthCount + 1
End If
Select Case intDay

Case 2 'Mondays
intMondays = intMondays + 1

Case 3 'Tuesdays
intTuesdays = intTuesdays + 1

Case 4 'Wednesdays
intWednesdays = intWednesdays + 1

Case 5 'Thursdays
intThursdays = intThursdays + 1

Case 6 'Fridays
intFridays = intFridays + 1

Case Else

End Select

dteDateStart = dteDateStart + 1
intTotDays = intTotDays + 1
Loop

With Count
.Mon = intMondays
.Tue = intTuesdays
.Wed = intWednesdays
.Thu = intThursdays
.Fri = intFridays
.Months = intMonthCount
.TotDays = intTotDays
End With

HowManySpecificDays = Count

On Error GoTo 0
Exit Function

HowManySpecificDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
HowManySpecificDays"

End Function
'************************************************* **
' /Version 1
'************************************************* **

But this is better
'************************************************* **
' Version 2
'************************************************* **
' In a module on it's own
Public Type DayCounts
Mon As Long
Tue As Long
Wed As Long
Thu As Long
Fri As Long
Months As Long
TotDays As Long
End Type

Public Function HowManySpecificDays(StartDate As Date, EndDate As Date) As
DayCounts

On Error GoTo HowManySpecificDays_Error

Dim dteDateStart As Date
Dim dteDateEnd As Date

Dim intMondays As Integer
Dim intTuesdays As Integer
Dim intWednesdays As Integer
Dim intThursdays As Integer
Dim intFridays As Integer
Dim intTotDays As Integer

Dim intDay As Integer
Dim intMonth As Integer
Dim intMonthCount As Integer

dteDateStart = Format(StartDate, "dd/mm/yyyy")

dteDateEnd = Format(EndDate, "dd/mm/yyyy")

intMonthCount = 1
intMonth = Month(dteDateStart)

'Loop through the date range and work out how many days in the period
Do Until dteDateStart = dteDateEnd + 1

intDay = Weekday(dteDateStart)

If Not intMonth = Month(dteDateStart) Then
intMonth = Month(dteDateStart)
intMonthCount = intMonthCount + 1
End If
Select Case intDay

Case 2 'Mondays
intMondays = intMondays + 1

Case 3 'Tuesdays
intTuesdays = intTuesdays + 1

Case 4 'Wednesdays
intWednesdays = intWednesdays + 1

Case 5 'Thursdays
intThursdays = intThursdays + 1

Case 6 'Fridays
intFridays = intFridays + 1

Case Else

End Select

dteDateStart = dteDateStart + 1
intTotDays = intTotDays + 1
Loop

With HowManySpecificDays
.Mon = intMondays
.Tue = intTuesdays
.Wed = intWednesdays
.Thu = intThursdays
.Fri = intFridays
.Months = intMonthCount
.TotDays = intTotDays
End With
On Error GoTo 0
Exit Function

HowManySpecificDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
HowManySpecificDays"

End Function

' Then called like this from any module
Private Sub CalcDays_Click()
Dim Count As DayCounts
Count = HowManySpecificDays("01/01/2002", "31/12/2002")

Debug.Print "Between these 2 dates there are:"
Debug.Print Count.Mon & " Mondays"
Debug.Print Count.Tue & " Tuesdays"
Debug.Print Count.Wed & " Wednesdays"
Debug.Print Count.Thu & " Thursdays"
Debug.Print Count.Fri & " Fridays"
Debug.Print Count.Mon + Count.Tue + Count.Wed + Count.Thu +
Count.Fri & " Chargeable Days"
Debug.Print Count.TotDays & " Total Days"
End Sub
'************************************************* **
' /Version 2
'************************************************* **

Although whether the code for the HowManySpecificDays function is correct I
wouldn't like to say.

Terry
"David Mitchell" <da**************@talk21.com> wrote in message
news:c3*************************@posting.google.co m...
I have tried using the following code to count the specific number of
each weekday but get a compile error "User defined type not defined"
which I think relates to the first line of the function: -

Public Function HowManySpecificDays(StartDate As Date, EndDate As
Date) As DayCounts

and specifically the word "DayCounts"

Can anyone help?

Full code is as follows.

Private Sub CalcDays_Click()

Call HowManySpecificDays("01/01/2002", "31/12/2002")

Debug.Print "Between these 2 dates there are:"
Debug.Print Count.Mon & " Mondays"
Debug.Print Count.Tue & " Tuesdays"
Debug.Print Count.Wed & " Wednesdays"
Debug.Print Count.Thu & " Thursdays"
Debug.Print Count.Fri & " Fridays"
Debug.Print Count.Mon + Count.Tue + Count.Wed + Count.Thu +
Count.Fri & " Chargeable Days"
Debug.Print Count.TotDays & " Total Days"
End Sub
Public Function HowManySpecificDays(StartDate As Date, EndDate As
Date) As DayCounts

On Error GoTo HowManySpecificDays_Error

Dim dteDateStart As Date
Dim dteDateEnd As Date

Dim intMondays As Integer
Dim intTuesdays As Integer
Dim intWednesdays As Integer
Dim intThursdays As Integer
Dim intFridays As Integer
Dim intTotDays As Integer

Dim intDay As Integer
Dim intMonth As Integer
Dim intMonthCount As Integer

dteDateStart = Format(StartDate, "dd/mm/yyyy")

dteDateEnd = Format(EndDate, "dd/mm/yyyy")

intMonthCount = 1
intMonth = Month(dteDateStart)

'Loop through the date range and work out how many days in the
period
Do Until dteDateStart = dteDateEnd + 1

intDay = WeekDay(dteDateStart)

If Not intMonth = Month(dteDateStart) Then
intMonth = Month(dteDateStart)
intMonthCount = intMonthCount + 1
End If
Select Case intDay

Case 2 'Mondays
intMondays = intMondays + 1

Case 3 'Tuesdays
intTuesdays = intTuesdays + 1

Case 4 'Wednesdays
intWednesdays = intWednesdays + 1

Case 5 'Thursdays
intThursdays = intThursdays + 1

Case 6 'Fridays
intFridays = intFridays + 1

Case Else

End Select

dteDateStart = dteDateStart + 1
intTotDays = intTotDays + 1
Loop

With Count
.Mon = intMondays
.Tue = intTuesdays
.Wed = intWednesdays
.Thu = intThursdays
.Fri = intFridays
.Months = intMonthCount
.TotDays = intTotDays
End With

HowManySpecificDays = Count

On Error GoTo 0
Exit Function

HowManySpecificDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure HowManySpecificDays"

End Function

Nov 12 '05 #2

P: n/a
"Terry Kreft" <te*********@mps.co.uk> wrote in message news:<bn**********@newsreaderm1.core.theplanet.net >...
You haven't got a declaration for Count.


Terry,

I use this group daily, both to answer questions I have and to
increase my knowledge of all things Access. All to often someone
takes the time to post an answer to a question and there is no follow
up from the original poster.

Thank you, thank you, thank you!

Your solution works perfectly and has transformed my application.

Very best regards

David
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.