Search Form Parameter Not Working

[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,

 

[Marshall Barton]

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

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.

 

[Londa Sue]

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.)

 

[Marshall Barton]

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

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.

 

[Londa Sue]

Works like a dream.

Thank you!

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.