Searching via combo box

[Daniel]

Hi, I am setting up a search function in the header of one of my forms and
want to display the results in the details section. Here is what I have so
far (this comes straight from Allen Brownes example):

If Me.cboLevel = "A" Then
strA = "A"
strWhere = strWhere & "([Level] = strA ) AND "
ElseIf Me.cboLevel = "B" Then
strB = "B"
strWhere = strWhere & "([Level] = strB) AND "
End If

If Not IsNull(Me.cboCompanyName) Then
strWhere = strWhere & "([CompanyName] = True) AND "
Else
strWhere = strWhere & "([CompanyName] = False) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub


For some reason, when I choose my company from the combo box, it doesn't
filter properly. I have another button that displays all records with no
filter and that works just fine.

I also want to be able to filter by "Level", which I have in my table as
being either A or B, which are text values. When I choose this in the combo
box it pops up a message saying "Enter parameter value".

Any help would be greatly appreciated!

 

[Carl Rapson]

Hi, I am setting up a search function in the header of one of my forms and
want to display the results in the details section. Here is what I have
so
far (this comes straight from Allen Brownes example):

If Me.cboLevel = "A" Then
strA = "A"
strWhere = strWhere & "([Level] = strA ) AND "
ElseIf Me.cboLevel = "B" Then
strB = "B"
strWhere = strWhere & "([Level] = strB) AND "
End If

If Not IsNull(Me.cboCompanyName) Then
strWhere = strWhere & "([CompanyName] = True) AND "
Else
strWhere = strWhere & "([CompanyName] = False) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub


For some reason, when I choose my company from the combo box, it doesn't
filter properly. I have another button that displays all records with no
filter and that works just fine.

I also want to be able to filter by "Level", which I have in my table as
being either A or B, which are text values. When I choose this in the
combo
box it pops up a message saying "Enter parameter value".

Any help would be greatly appreciated!

When you're building a SQL string like this, you need to actually
concatenate the values that you're interested in. So, for example, for the
Level, you could say:

strWhere = strWhere & "([Level] = '" & Me.cboLevel & "') AND "

There's no need to add the extra step of using the strA and strB variables.
Note that we also added single quotes around the level value, since it's a
string. At this point, your strWhere string would look something like this:

[Level] = 'A' AND

Similarly, with the CompanyName, you need something like:

If Not IsNull(Me.cboCompanyName) Then
strWhere = strWhere & "([CompanyName] = '" & Me.cboCompanyName & "') AND
"
End If

Again, note the single quotes around the company name. Note also that you
don't need to worry about the case where no company is selected.

Carl Rapson