| |
You are here: Experts > Computing/Technology > Business Software > Using MS Access > Filtering Report by Form
Expert: Scottgem - 11/6/2009
Question
QUESTION: Hi Scott,
I am using a form to filter a report. On the form there are two unbound combo boxes to filter the report where the user can select both fields or one or just one and then presses a button to open the report. I am using the following codes:
Private Sub Form_Load()
For Each ctl In Me.Controls
If TypeOf ctl Is ComboBox Then
ctl.SetFocus
ctl.Text = ""
End If
Next
End Sub
Private Sub Report_Click()
On Error GoTo Err_Report_Click
Dim stDocName, sqlString As String
stDocName = "R_Requests_Full_List"
sqlString = ""
For Each ctl In Me.Controls
If TypeOf ctl Is ComboBox Then
ctl.SetFocus
If ctl.Text <> "" Then
If sqlString = "" Then
sqlString = ctl.Name & "=" & """" & ctl.Text & """"
Else
sqlString = sqlString & " and " & ctl.Name & "=" & """" & ctl.Text & """"
End If
End If
End If
Next
DoCmd.OpenReport stDocName, acPreview, , sqlString
Exit_Report_Click:
Exit Sub
Err_Report_Click:
MsgBox Err.Description
Resume Exit_Report_Click
End Sub
On the report I have a date due field and a date requested field. What I want to do is replace one of the combo boxes with text boxes to filter between two dates. For example display records in the report where the date due is between first date (text box) and second date (text box). I’d like to have the same thing for the date requested field as well. I still want the user to have the option of filtering by just one of the fields or all of them. Such as selecting a Customer from the combo and filling in a due date period to search by or just filling in the two date periods.
I don’t want to adjust the report query as this is called at different points in the database which is why I am using a form to filter the report. So is there a way that this code can be adjusted to do that?
Thanks in advance!
ANSWER: I'm not sure why you are using the code you showed. What you need is something like this:
Private Sub Report_Click()
On Error GoTo Err_Report_Click
Dim stDocName, sqlString As String
stDocName = "R_Requests_Full_List"
sqlString = ""
For Each ctl In Me.Controls
If Not IsNull(ctl.Value) Then
sqlString = sqlstring & "[fieldname] = " & ctl.Value & " "
End If
Next
DoCmd.OpenReport stDocName, acPreview, , sqlString
Exit_Report_Click:
Exit Sub
Err_Report_Click:
MsgBox Err.Description
Resume Exit_Report_Click
End Sub
You may need to add a check for the control's Name property to adjust how you add to the SQL string. I'd probably use a SELECT CASE for that.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: Hi Scott,
Thanks for the prompt answer!
Can you please give an example of how you would use a select case to check for the control's name property to adjust the how the sql string is added?
Answer
If Not IsNull(ctl.Value) Then
If sqlString <> "" Then
sql String = sqlSttring & " AND "
End If
Select Case ctl.Name
Case "cboCustomer"
sqlString = sqlString & "[CustomerID] = " & ctl.Value
Case "DueDate"
sqlString = sqlString & "[DueDate] = " & ctl.Value
Case "StartDate"
sqlString = sqlString & "[DueDate] > " & ctl.Value
Case "endDate"
sqlString = sqlString & "[DueDate] < " & ctl.Value
Case Else
End Select
End If
Disclaimer this is "aircode" and may not be syntactically correct, but it should give you the direction you need. Also remember to use your own names for objects.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
Add to this Answer Ask a Question
|
|
