I am trying to develop a filter dialog form for a filtering report. I have found a few examples but I don't understand them. THis is the code I found for one of them but I am not sure how to adapt it.
Report is called All Employee Report.
I have a yes/no field called ActiveFlag. I would like to filter the report based on this. The code I found is as follows but its working with strings which is confusing me.
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strReport As String
Dim strWhere As String
Dim strState As String
Dim strWhereDate As String
' This is one way you can build a SQL "Where" Statement
If Me.chkCalifornia = True Then
strState = strState & ",'CA'"
End If
If Me.chkFlorida = True Then
strState = strState & ",'FL'"
End If
If Me.chkNorthCarolina = True Then
strState = strState & ",'NC'"
End If
If Me.chkTennessee = True Then
strState = strState & ",'TN'"
End If
' If the first part of strState is ,
' We know that strState has a value to use
If Left(strState, 1) = "," Then
' Remove the ,
strState = Right(strState, Len(strState) - 1)
' Add the beginning part of the Where Statement
strState = " AND State IN (" & strState
' Add the end part of the Where Statement
strState = strState & ")"
Else
' If the , was not added to strState then
' No value was there--clear strState
strState = vbNullString
End If
' Debug.Print "strState = " & strState
' Build the Date Filter
Select Case cboDateFilter
Case Is = "MarriageDate"
strWhereDate = "((tblPeople.MarriageDate) >= [Forms]![frmReportDialog]![txtStartDate])" & _
" AND ((tblPeople.MarriageDate)<=[Forms]![frmReportDialog]![txtEndDate])"
Case Is = "DOB"
strWhereDate = "((tblPeople.DOB) >= [Forms]![frmReportDialog]![txtStartDate])" & _
" AND ((tblPeople.DOB)<=[Forms]![frmReportDialog]![txtEndDate])"
Case Is = "None"
strWhereDate = vbNullString
Case Else
strWhereDate = vbNullString
End Select
' Debug.Print "strWhereDate = " & strWhereDate
' Put together the SQL string
strWhere = strWhereDate & strState
' Debug.Print "strWhere = " & strWhere
' Trim "AND " from the beginning if needed
If Left(strWhere, 4) = " AND" Then
' intWhereLen = Len(strWhere) - 4
strWhere = Right(strWhere, Len(strWhere) - 4)
End If
' Debug.Print "strWhere = " & strWhere
strReport = Me.lstReports
' Debug.Print "strReport = " & strReport
DoCmd.OpenReport strReport, View:=acViewPreview, WhereCondition:=strWhere
' To use these 2 lines of code below you must add
' DoCmd.Restore to your Report's On Close Event,
DoCmd.RunCommand acCmdZoom75
DoCmd.Maximize
Exit_cmdOpenReport_Click:
Exit Sub
Err_cmdOpenReport_Click:
Select Case Err.Number
Case Is = 2501
'Ignore Useless Error Messsage
Resume Next
Case Is = 2046
'Restore to Normal Window size
DoCmd.Restore
Case Else
Call MsgBox(Err.Description & vbCrLf & "Error Number: " & Err.Number & vbCrLf & _
" In procedure cmdOpenReport_Click of VBA Document Form_frmReportDialog")
Resume Exit_cmdOpenReport_Click
End Select
End Sub
I don't need any of the date filters but I need to adjust it for the checkbox.
Thanks,
Aert Lorenzini
Sioux Falls. SD
Tidak ada komentar:
Posting Komentar