Searching via combo box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
Daniel said:
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!
Click to expand...

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
 
Back
Top