Shane02
01-15-2015, 02:43 PM
Hello, I am trying to create a search form to launch a report with specific records. This is for a simple database to be used for tracking vehicle maintenance.
I've created a form following a tutorial by allenbrowne.com and can successfully filter for date ranges but I need to also be able to filter for "Vehicle Number" which is actually a short text field because a few vehicles are listed by name. I have created an unbound text box on the form named txtVehicle for this purpose.
This is the working code for searching for the date range:
Option Compare Database
Private Sub cmdPreview_Click()
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Maintenance Report"
strDateField = "[Date of Service]"
lngView = acViewPreview
If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If
DoCmd.OpenReport strReport, lngView, , strWhere
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub
I have tried adding arguments to also allow the user to enter a vehicle number or name but nothing I've tried has worked. It would also be great if the field is left blank it would return all records in the date range but I'd settle just for getting the basic query working. I am familiar with Access but know very little about VB or coding in general. Any suggestions would be greatly appreciated.
Thanks,
Shane
I've created a form following a tutorial by allenbrowne.com and can successfully filter for date ranges but I need to also be able to filter for "Vehicle Number" which is actually a short text field because a few vehicles are listed by name. I have created an unbound text box on the form named txtVehicle for this purpose.
This is the working code for searching for the date range:
Option Compare Database
Private Sub cmdPreview_Click()
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"
strReport = "Maintenance Report"
strDateField = "[Date of Service]"
lngView = acViewPreview
If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If
DoCmd.OpenReport strReport, lngView, , strWhere
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub
I have tried adding arguments to also allow the user to enter a vehicle number or name but nothing I've tried has worked. It would also be great if the field is left blank it would return all records in the date range but I'd settle just for getting the basic query working. I am familiar with Access but know very little about VB or coding in general. Any suggestions would be greatly appreciated.
Thanks,
Shane