clinthammer
asked on
Report not opening with filter condition - need VBA filter help
I have a search form with 5 fields. The user enters data in one or more fields and presses search. A vba filter takes this entered data and uses it as a condition when opening the report.
Report data is basically based on the search form.
This is the data type of the fields mentioned:
[Media Category] = text
[Advertising PR Genre] = text - combo box taking data from anoter table
[Advertising Agency Type] =text -combo box taking data from anoter table
[Country] = text - combo box taking data from anoter table
[Name] = text
The table containing those fields is called Advertising and PR. The fields are in a search form entitled frmSearchAdvertisingPR.
The field Media Category is always set to Advertising PR. for the other fields the user selects the criteria from the combo box or if its just a text field, the user types in the criteria.
When search is pressed the following code is run:
--------------------------
Private Sub Command17_Click()
Dim strFilter As String
Dim strMsg As String
If IsNull(Me.cmbAgencyType) And IsNull(Me.cmbCountry) And _
IsNull(Me.cmbGenre) And IsNull([txtName]) Then
strMsg = MsgBox("Please select or enter a search term!", vbOKOnly)
Exit Sub
End If
strFilter = ""
If Not IsNull(Me!txtMediaCategory
strFilter = strFilter & "[Media Category] like '*" & Me!txtMediaCategory & "*' AND "
End If
If Not IsNull(Me!cmbGenre.Value) Then
strFilter = strFilter & "[Advertising PR Genre] = " & Me!cmbGenre.Value & " AND "
End If
If Not IsNull(Me!cmbAgencyType.Va
strFilter = strFilter & "[Advertising Agency Type] = " & Me!cmbAgencyType.Value & " AND "
End If
If Not IsNull(Me!cmbCountry.Value
strFilter = strFilter & "[Country] = " & Me!cmbCountry.Value & " AND "
End If
If Not IsNull(Me!txtName) Then
strFilter = strFilter & "[Name] like '*" & Me!txtName & "*' AND "
End If
If strFilter <> "" Then
strFilter = Left$(strFilter, Len(strFilter) - 4)
strMsg = MsgBox(strFilter, vbOKOnly)
End If
On Error GoTo ErrorHandler
DoCmd.OpenReport "rptAdvertisingPR", acViewPreview, , strFilter
ExitHandler:
Exit Sub
ErrorHandler:
'This traps the Output to Error message
If Err = 2501 Then
Resume ExitHandler
Else
MsgBox Err.Description
Resume ExitHandler
End If
End Sub
--------------------------
The report rptAdvertisingPr should open with criteria from the strfilter but it doesn't.
If I select only critera in the AdvertisingPR Genre combo box and press search it gives me a syntax error.
If I select criteria in either Advertising Agency Type or Country, it asks me to Enter Parameter Value.
If I enter criteria in the Name field only - the report opens.
Why am I getting these syntax or enter parameter errors?
Report data is basically based on the search form.
This is the data type of the fields mentioned:
[Media Category] = text
[Advertising PR Genre] = text - combo box taking data from anoter table
[Advertising Agency Type] =text -combo box taking data from anoter table
[Country] = text - combo box taking data from anoter table
[Name] = text
The table containing those fields is called Advertising and PR. The fields are in a search form entitled frmSearchAdvertisingPR.
The field Media Category is always set to Advertising PR. for the other fields the user selects the criteria from the combo box or if its just a text field, the user types in the criteria.
When search is pressed the following code is run:
--------------------------
Private Sub Command17_Click()
Dim strFilter As String
Dim strMsg As String
If IsNull(Me.cmbAgencyType) And IsNull(Me.cmbCountry) And _
IsNull(Me.cmbGenre) And IsNull([txtName]) Then
strMsg = MsgBox("Please select or enter a search term!", vbOKOnly)
Exit Sub
End If
strFilter = ""
If Not IsNull(Me!txtMediaCategory
strFilter = strFilter & "[Media Category] like '*" & Me!txtMediaCategory & "*' AND "
End If
If Not IsNull(Me!cmbGenre.Value) Then
strFilter = strFilter & "[Advertising PR Genre] = " & Me!cmbGenre.Value & " AND "
End If
If Not IsNull(Me!cmbAgencyType.Va
strFilter = strFilter & "[Advertising Agency Type] = " & Me!cmbAgencyType.Value & " AND "
End If
If Not IsNull(Me!cmbCountry.Value
strFilter = strFilter & "[Country] = " & Me!cmbCountry.Value & " AND "
End If
If Not IsNull(Me!txtName) Then
strFilter = strFilter & "[Name] like '*" & Me!txtName & "*' AND "
End If
If strFilter <> "" Then
strFilter = Left$(strFilter, Len(strFilter) - 4)
strMsg = MsgBox(strFilter, vbOKOnly)
End If
On Error GoTo ErrorHandler
DoCmd.OpenReport "rptAdvertisingPR", acViewPreview, , strFilter
ExitHandler:
Exit Sub
ErrorHandler:
'This traps the Output to Error message
If Err = 2501 Then
Resume ExitHandler
Else
MsgBox Err.Description
Resume ExitHandler
End If
End Sub
--------------------------
The report rptAdvertisingPr should open with criteria from the strfilter but it doesn't.
If I select only critera in the AdvertisingPR Genre combo box and press search it gives me a syntax error.
If I select criteria in either Advertising Agency Type or Country, it asks me to Enter Parameter Value.
If I enter criteria in the Name field only - the report opens.
Why am I getting these syntax or enter parameter errors?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I removed this from the code:
If Not IsNull(Me!txtMediaCategory
strFilter = strFilter & "[Media Category] like '*" & Me!txtMediaCategory & "*' AND "
End If
but no change.
So I tried this. From the combo for Advertising PR Genre, I select Advertising Agency. Media Category is always Advertising PR.
I pressed search and got this:
Syntax error (missing operator) in query expression '([Media Category] like '*Advertising PR*' AND [Advertising PR Genre] = Advertising Agency)'
If Not IsNull(Me!txtMediaCategory
strFilter = strFilter & "[Media Category] like '*" & Me!txtMediaCategory & "*' AND "
End If
but no change.
So I tried this. From the combo for Advertising PR Genre, I select Advertising Agency. Media Category is always Advertising PR.
I pressed search and got this:
Syntax error (missing operator) in query expression '([Media Category] like '*Advertising PR*' AND [Advertising PR Genre] = Advertising Agency)'
ASKER
Well bonjour-aut I didnt apply your input exactly but it assisted me in finding what was wrong.
If Not IsNull(Me!cmbGenre.Value) Then
strFilter = strFilter & "[Advertising PR Genre] = " & Me!cmbGenre.Value & " AND "
End If
should be
If Not IsNull(Me!cmbGenre.Value) Then
strFilter = strFilter & "[Advertising PR Genre] = '" & Me!cmbGenre.Value & "' AND "
End If
i forgot the ' before and after the "
Will test this again for my other 3 forms and award vous the points.
Merci beaucoup :)
If Not IsNull(Me!cmbGenre.Value) Then
strFilter = strFilter & "[Advertising PR Genre] = " & Me!cmbGenre.Value & " AND "
End If
should be
If Not IsNull(Me!cmbGenre.Value) Then
strFilter = strFilter & "[Advertising PR Genre] = '" & Me!cmbGenre.Value & "' AND "
End If
i forgot the ' before and after the "
Will test this again for my other 3 forms and award vous the points.
Merci beaucoup :)
strFilter = strFilter & "[Media Category] like '*" & Me!txtMediaCategory & "*' AND "
TO
strFilter = strFilter & "[Media Category] like '" & Me!txtMediaCategory & "*' AND "
Aziz