I have a filter form that lets the user define the filters they want the report to have, but I need a way to show all if they don't want to filter out information. The fitler options are radio buttons and the user can choose one option and I would like to have an "All" option, but I can't get the underlying query to show all. Can anyone help or tell me what to search for. I have looked all over the forum and can't seem to find anything that pertains to my question. Thanks in advance.
Filter Form w/ Option to Show All
- Thread starter mrssevans
- Start date
mrssevans said:
This is what I do. I have a null string. For every option checked, I add to the string the criteria. Then I open the report which either has criteria, or a null string.
The problem is that the first criteria is simply stated where as the rest of the criteria is "AND ....". To overcome this if your string is null, just have the first criteria as "isnull(primarykeyfield) = false " which will get all data anyway.
In my example, next to each check box, I have a text box. So if the textbox is checked, it filters that field to the value in the textbox.
Dim check
check = Null
'If option checked, add a string to the report so it looks up the staff
If OptStaff = True Then
check = check & "AND StaffName = '" & txtStaff & "' "
End If
'If option checked, add a string to the report so it looks up area
If OptArea = True Then
check = check & "AND Area = '" & txtArea & "' "
End If
'........etc........
'Get the report based on the above criteria
DoCmd.OpenReport "myReport", acPreview, , "isnull(primarykeyfield) = false " & check
I think this code is going to work great for me, but I am running into one little snag. I only want to allow the user to filter for 1 option in a group at a time, but when I use the code listed below it will only show the s in the sex column. Even if I change that to H (for Heifers) it still will only show the steers on the report. Does anyone see a problem with the code?
Private Sub Command11_Click()
Dim check
check = Null
If Option13 = True Then
check = check & "AND Sex = '" & "S" & "' "
End If
If Option15 = True Then
check = check & "AND Sex = '" & "H" & "' "
End If
On Error GoTo Err_Command11_Click
Dim stDocName As String
stDocName = "Closeout"
DoCmd.OpenReport stDocName, acPreview, , "isnull(ID) = false " & check
Exit_Command11_Click:
Exit Sub
Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click
End Sub
Private Sub Command11_Click()
Dim check
check = Null
If Option13 = True Then
check = check & "AND Sex = '" & "S" & "' "
End If
If Option15 = True Then
check = check & "AND Sex = '" & "H" & "' "
End If
On Error GoTo Err_Command11_Click
Dim stDocName As String
stDocName = "Closeout"
DoCmd.OpenReport stDocName, acPreview, , "isnull(ID) = false " & check
Exit_Command11_Click:
Exit Sub
Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click
End Sub
