Search Form to Set Records For Report

[Shane02]

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

[jonh]

varStart = Trim("" & txtStartDate)
varEnd = Trim("" & txtEndDate)
strVNum = Trim("" & txtVNum)

If IsDate(varStart) Then
varStart = Format(CDate(varStart), "\#mm/dd/yyyy\#")
If IsDate(varEnd) Then
strWhere = "[Date of Service] between " & varStart & _
" and " & Format(CDate(varEnd), "\#mm/dd/yyyy\#")
Else
If varEnd = "" Then
strWhere = "[Date of Service] >= " & varStart
Else
strErr = "End date is not a valid date."
End If
End If
Else
strErr = "Start date is not a valid date."
End If

If Len(strErr) Then
MsgBox strErr, vbExclamation, "Check inputs"
Exit Sub
End If

If Len(Trim(strVNum)) Then
If Len(strWhere) Then strWhere = strWhere & " and "
strWhere = strWhere & "[Vehicle Number]='" & strVNum & "'"
End If

If Len(strWhere) Then
Debug.Print strWhere
Else
Debug.Print "no filter set"
End If

If CurrentProject.AllReports(strReport).IsLoaded Then
If MsgBox("A copy of this report is already open. Continue?", vbQuestion + vbYesNo) = vbYes Then
DoCmd.Close acReport, "Maintenance Report"
Else
Exit Sub
End If
End If

DoCmd.OpenReport "Maintenance Report", acViewPreview, , strWhere

[Shane02]

jonh, thanks so much for the help! That works perfectly!