Search Form Parameter Not Working

  • Thread starter Thread starter Londa Sue
  • Start date Start date
L

Londa Sue

Hello,

I've a search form in the database on which users can search on one of three
criteria: process area, practice, or practice satisfaction.

The form works well for process area and practice, but the search using
practice satisfaction is not working. Returns no values.

Here is the code:

Private Function BuildWhereString() As String
Dim strWhere As String
Dim strSQL As String
Dim varItemSel As Variant

On Error Resume Next

strWhere = ""

' ... build "process area" criterion expression
If (Nz(Me.cboProcessArea.Value, "") <> "") Then
strWhere = strWhere & "ProcessArea='" & Me.cboProcessArea.Value & "' And "
End If

' ... build "cmmi practice" criterion expression
If (Nz(Me.cboCMMIPractice.Value, "") <> "") Then
strWhere = strWhere & "CMMIPractice='" & Me.cboCMMIPractice.Value & "' And "
End If

' ... build "practice satisfied" criterion expression
If (Nz(Me.cboPracticeSatisfied.Value, "") <> "") Then
strWhere = strWhere & "PracticeSatisfied='" &
Me.cboPracticeSatisfied.Value & "' And "
End If

' Strip off the trailing " And " text string
If Len(strWhere) > 0 Then strWhere = Left(strWhere, Len(strWhere) - _
Len(" And "))

BuildWhereString = strWhere
Exit Function
End Function

Any help on this is appreciated,
 
Londa said:
I've a search form in the database on which users can search on one of three
criteria: process area, practice, or practice satisfaction.

The form works well for process area and practice, but the search using
practice satisfaction is not working. Returns no values.

Here is the code:

Private Function BuildWhereString() As String
Dim strWhere As String
Dim strSQL As String
Dim varItemSel As Variant

On Error Resume Next

strWhere = ""

' ... build "process area" criterion expression
If (Nz(Me.cboProcessArea.Value, "") <> "") Then
strWhere = strWhere & "ProcessArea='" & Me.cboProcessArea.Value & "' And "
End If

' ... build "cmmi practice" criterion expression
If (Nz(Me.cboCMMIPractice.Value, "") <> "") Then
strWhere = strWhere & "CMMIPractice='" & Me.cboCMMIPractice.Value & "' And "
End If

' ... build "practice satisfied" criterion expression
If (Nz(Me.cboPracticeSatisfied.Value, "") <> "") Then
strWhere = strWhere & "PracticeSatisfied='" & Me.cboPracticeSatisfied.Value & "' And "
End If

' Strip off the trailing " And " text string
If Len(strWhere) > 0 Then strWhere = Left(strWhere, Len(strWhere) - _
Len(" And "))

BuildWhereString = strWhere
Exit Function
End Function
Click to expand...


On the surface, I don't see anything wrong in your code.

Are you sure that PracticeSatisfied is a Text field? If
it's a number type, then it should not be in quotes.

Also, double check that you have set cboPracticeSatisfied
BoundColumn property correctly.
 
Thanks.

It's a number field. Which quotes should be removed? (Sorry for the
ignorance, but the ones I removed didn't return the values.)
 
Londa said:
It's a number field. Which quotes should be removed? (Sorry for the
ignorance, but the ones I removed didn't return the values.)
Click to expand...


If (Nz(Me.cboPracticeSatisfied.Value, "") <> "") Then
strWhere = strWhere & "PracticeSatisfied=" _
& Me.cboPracticeSatisfied.Value & " And "
End If

Double check the other fields and remove the quotes
(apostrophes) for any others that are a number type field.
 
Works like a dream.

Thank you!

Marshall Barton said:
If (Nz(Me.cboPracticeSatisfied.Value, "") <> "") Then
strWhere = strWhere & "PracticeSatisfied=" _
& Me.cboPracticeSatisfied.Value & " And "
End If

Double check the other fields and remove the quotes
(apostrophes) for any others that are a number type field.
Click to expand...
 
Back
Top