craigglenn
Technical User
Ok, I have searched and just can't figure this out. The problem is that I need to order a report on a field that is determined by user imput for a certain day they chose. The basics...
Property Route report based on seven days
Get the day user want to print the report for
get the route number to report on
since properties are sweep on different nights and my be on different routes i had to have fields for the route number and priority for each property for that route.
for example property abc may be sweep on M,W,F
on Monday it is on Route 1
On wednesday it is on Route 2
then Friday back on route 1
the key is to sort each route with a priorty field supplied based on day and route. Her is my code. How do I reference the priority field in the query?
Thanks in advance.
Private Sub Report_NoData(Cancel As Integer)
MsgBox ("No records found for specified route."
Cancel = True
End Sub
Private Sub Report_Open(Cancel As Integer)
Dim routeN As Integer
Dim rDay As Integer
Dim theQuery As String
Dim dayField As String
Dim priority As String
GetRouteDay rDay 'prompt for route day
GetRouteNum routeN 'prompt for route number
Select Case rDay
Case 1
dayField = "PropSwpMonday"
priority = PropPriorityMonday
Case 2
dayField = "PropSwpTuesday"
priority = [PropPriorityTuesday]
Case 3
dayField = "PropSwpWednesday"
priority = PropPriorityWednesday
Case 4
dayField = "PropSwpThursday"
priority = PropPriorityThursday
Case 5
dayField = "PropSwpFriday"
priority = PropPriorityFriday
Case 6
dayField = "PropSwpSaturday"
priority = PropPrioritySaturday
Case 7
dayField = "PropSwpSunday"
priority = PropPrioritySunday
End Select
theQuery = "Select * From mainProperty Where " & dayField & "=" & routeN & " Order By " & priority & ";"
Me.RecordSource = theQuery
End Sub
Private Function GetRouteNum(routeN) As Integer
On Error GoTo ErrorHandler
Dim message, title
message = "Enter Route Number:"
title = "Route Number"
routeN = InputBox(message, title)
Exit Function
ErrorHandler:
Select Case Err.Number
Case 13
MsgBox ("You must enter a valid route number"
Case Else
MsgBox ("You must enter a valid route number"
End Select
Resume
End Function
Private Function GetRouteDay(rDay) As Integer
On Error GoTo ErrorHandler
Dim message, title
message = "Enter number for day of route:" + (Chr(13)) + "1. Monday" + (Chr(13)) + "2. Tuesday" + (Chr(13)) + "3. Wednesday" + (Chr(13)) + "4. Thursday" + (Chr(13)) + "5. Friday" + (Chr(13)) + "6. Saturday" + (Chr(13)) + "7. Sunday"
title = "Route day"
InvalidDay:
rDay = InputBox(message, title)
Select Case rDay
Case "1", "2", "3", "4", "5", "6", "7"
Exit Function
Case Else
MsgBox ("You must enter a valid Day Number"
GoTo InvalidDay
End Select
ErrorHandler:
Select Case Err.Number
Case 13
MsgBox ("You must enter a valid day"
Case Else
MsgBox ("You must enter a valid day"
End Select
Resume
End Function
Property Route report based on seven days
Get the day user want to print the report for
get the route number to report on
since properties are sweep on different nights and my be on different routes i had to have fields for the route number and priority for each property for that route.
for example property abc may be sweep on M,W,F
on Monday it is on Route 1
On wednesday it is on Route 2
then Friday back on route 1
the key is to sort each route with a priorty field supplied based on day and route. Her is my code. How do I reference the priority field in the query?
Thanks in advance.
Private Sub Report_NoData(Cancel As Integer)
MsgBox ("No records found for specified route."
Cancel = True
End Sub
Private Sub Report_Open(Cancel As Integer)
Dim routeN As Integer
Dim rDay As Integer
Dim theQuery As String
Dim dayField As String
Dim priority As String
GetRouteDay rDay 'prompt for route day
GetRouteNum routeN 'prompt for route number
Select Case rDay
Case 1
dayField = "PropSwpMonday"
priority = PropPriorityMonday
Case 2
dayField = "PropSwpTuesday"
priority = [PropPriorityTuesday]
Case 3
dayField = "PropSwpWednesday"
priority = PropPriorityWednesday
Case 4
dayField = "PropSwpThursday"
priority = PropPriorityThursday
Case 5
dayField = "PropSwpFriday"
priority = PropPriorityFriday
Case 6
dayField = "PropSwpSaturday"
priority = PropPrioritySaturday
Case 7
dayField = "PropSwpSunday"
priority = PropPrioritySunday
End Select
theQuery = "Select * From mainProperty Where " & dayField & "=" & routeN & " Order By " & priority & ";"
Me.RecordSource = theQuery
End Sub
Private Function GetRouteNum(routeN) As Integer
On Error GoTo ErrorHandler
Dim message, title
message = "Enter Route Number:"
title = "Route Number"
routeN = InputBox(message, title)
Exit Function
ErrorHandler:
Select Case Err.Number
Case 13
MsgBox ("You must enter a valid route number"
Case Else
MsgBox ("You must enter a valid route number"
End Select
Resume
End Function
Private Function GetRouteDay(rDay) As Integer
On Error GoTo ErrorHandler
Dim message, title
message = "Enter number for day of route:" + (Chr(13)) + "1. Monday" + (Chr(13)) + "2. Tuesday" + (Chr(13)) + "3. Wednesday" + (Chr(13)) + "4. Thursday" + (Chr(13)) + "5. Friday" + (Chr(13)) + "6. Saturday" + (Chr(13)) + "7. Sunday"
title = "Route day"
InvalidDay:
rDay = InputBox(message, title)
Select Case rDay
Case "1", "2", "3", "4", "5", "6", "7"
Exit Function
Case Else
MsgBox ("You must enter a valid Day Number"
GoTo InvalidDay
End Select
ErrorHandler:
Select Case Err.Number
Case 13
MsgBox ("You must enter a valid day"
Case Else
MsgBox ("You must enter a valid day"
End Select
Resume
End Function
