How to filter a form based on combo boxes?

[Steven547]

I have a form where the user can select a combo box which will filter the records that are brought back.
However, I receive the error:

"Run time error 3075 - Syntax error (missing operator) in query expression 'tblissues.issueID is >0 and Region = 'CO"."

Here is my code:

strSQL = ""
strSQL = "SELECT tblIssues.IssueID, tblIssues.Region, tblIssues.Category, tblIssues.[BETS Screen], tblIssues.[BETS Field Mapping], tblIssues.[CM Field Mapping], tblIssues.Issue, tblIssues.Description, tblIssues.Status, tblIssues.[Date Opened], tblIssues.[Date Closed], tblIssues.[Point of Contact], tblIssues.Priority, tblIssues.[Application Type], tblIssues.[Issue Area] FROM tblIssues Where tblIssues.IssueID is >0"

If Len([Forms]![frmMenu].[cboSearchIssueID] & "") > 0 Then
Me.RecordSource = strSQL & " And IssueID = "" & ([Forms]![frmMenu].[cboSearchIssueID])"
End If

If Len([Forms]![frmMenu].[cboSearchPriority] & "") > 0 Then
Me.RecordSource = strSQL & " And Priority = ' " & ([Forms]![frmMenu].[cboSearchPriority] & "'")
End If

If Len([Forms]![frmMenu].[cboSearchRegion] & "") > 0 Then
Me.RecordSource = strSQL & " And Region = ' " & ([Forms]![frmMenu].[cboSearchRegion] & "'")
End If

If Len([Forms]![frmMenu].[cboSearchStatus] & "") > 0 Then
Me.RecordSource = strSQL & " And Status = '" & ([Forms]![frmMenu].[cboSearchStatus] & "'")
End If

Been racking my brain over this all day and have had no luck.

Any ideas? Help?

thanks.

 

[Steven547]

Sorry...forgot to mention:

IssueId is an autonumber
Priority, Region, and Status are all strings. There is no "ID" in those lookup tables.

 

[MajP]

Does this look correct to you?
tblissues.issueID is >0 and Region = 'CO"

 

[Steven547]

That's the error I'm receiving...?

Region should be "CO" or whichever the user selects

 

[MajP]

Sorry I thought you would see it

TextField = 'textValue'
Region = 'CO'

NumberField = Number
ID = 123

DateField = #DateValue#
startDate = #1/1/2010#

So
And IssueID = "" & ([Forms]![frmMenu].[cboSearchIssueID])"
should be
And IssueID = " & [Forms]![frmMenu].[cboSearchIssueID]

I assume you want a string that looks like
"And Issue ID = 123"

And Region = ' " & ([Forms]![frmMenu].[cboSearchRegion] & "'")
should be

And Region = ' " & [Forms]![frmMenu].[cboSearchRegion] & "'"

I can not believe it even compiles with the parentheses outside of the string.

 

[MajP]

Before you post you need to do some error checking on your own
Google VBA error checking and "debug.print". You could have seen the problems immediately.


strSQL = ""
strSQL = "SELECT tblIssues.IssueID, tblIssues.Region, tblIssues.Category, tblIssues.[BETS Screen], tblIssues.[BETS Field Mapping], tblIssues.[CM Field Mapping], tblIssues.Issue, tblIssues.Description, tblIssues.Status, tblIssues.[Date Opened], tblIssues.[Date Closed], tblIssues.[Point of Contact], tblIssues.Priority, tblIssues.[Application Type], tblIssues.[Issue Area] FROM tblIssues Where tblIssues.IssueID is >0"

Debug.print strSql

If Len([Forms]![frmMenu].[cboSearchIssueID] & "") > 0 Then
Me.RecordSource = strSQL & " And IssueID = "" & ([Forms]![frmMenu].[cboSearchIssueID])"
End If

Debug.print "Search :" & strSql

If Len([Forms]![frmMenu].[cboSearchPriority] & "") > 0 Then
Me.RecordSource = strSQL & " And Priority = ' " & ([Forms]![frmMenu].[cboSearchPriority] & "'")
End If

Debug.print "Priority" & strSql

If Len([Forms]![frmMenu].[cboSearchRegion] & "") > 0 Then
Me.RecordSource = strSQL & " And Region = ' " & ([Forms]![frmMenu].[cboSearchRegion] & "'")
End If

Debug.print "Region : " & strSql

If Len([Forms]![frmMenu].[cboSearchStatus] & "") > 0 Then
Me.RecordSource = strSQL & " And Status = '" & ([Forms]![frmMenu].[cboSearchStatus] & "'")
End If

Debug.print "Status: " & strSql

This would all be clear as day with a little error checking and you would not have racked your brain all day.

 

[PHV]

Anyway, replace this:
Where tblIssues.IssueID is >0
with this:
Where tblIssues.IssueID > 0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

PHV shows the value of the debugging. I did not see that in code, but you and I would have likely saw that in a printed sql string.

 

[PHV]

PHV shows the value of the debugging
No, in the strSQL = "..." instruction.