Filter not working

[raugust]

I am trying to adapt an example to filter a form using a second form that collects both a field name (cboSearchField - all options are text controls) and field content (txtSearchString) and passes their contents to:

Private Sub cmdSearch_Click()

If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."

ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."

Else

'Generate search criteria
GCriteria = "Replace(" & cboSearchField.Value & ", chr(39), '')" & " LIKE '*" & Replace(txtSearchString, "'", "") & "*'"

'Filter frmPersonalMasterF based on search criteria
** Form_PersonalMasterF.RecordSource = "select * from ContactMasterT where " & GCriteria
Form_PersonalMasterF.Caption = "Customers (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

'Close frmSearch
DoCmd.Close acForm, "SearchF"

MsgBox "Results have been filtered."

End If

End Sub

I am getting an error message - Runtime error '3464'Data type mismatch in criteria expression. Debug points to the line marked **. Can anyone see my problem?

 

[pbaldy]

You can use this to see how the filter variable is coming out:

http://www.baldyweb.com/ImmediateWindow.htm

You wouldn't want the delimiters around a field with a numeric data type.

 

[raugust]

Debug.Print works as described - thanks - but still stumped by the data type mismatch error, specifically:

Run-time error '3464': Data type mismatch in criteria expression.

Here is my current code with a note on Debug.Print:

Private Sub cmdSearch_Click()

If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."
ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."
Else
'Generate search criteria
GCriteria = "Replace(" & cboSearchField.Value & ", chr(39), '')" & " LIKE '*" & Replace(txtSearchString, "'", "") & "*'"
'Filter Form_PersonlMasterF based on search criteria
Form_PersonalMasterF.RecordSource = "select * from ContactMasterT where " & GCriteria
'Debug stops here. Debug.Print result is Replace(LastName, chr(39), '') LIKE '*August*' LastName is a text field in ContactMasterT
Form_PersonalMasterF.Caption = "ContactMasterT (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

'Close frmSearch
DoCmd.Close acForm, "SearchF"

MsgBox "Results have been filtered."

End If

End Sub


Is my code expecting something other than text??

 

[pbaldy]

Offhand I don't see anything wrong, though my eyes are still crossed from working the weekend. Can you post the db here?

 

[raugust]

Here it is with sample data. The SearchF form is activated from the Search command button on PersonalMasterF, which at the moment will not go into form view because of the error condition.

 

[pbaldy]

I should have thought of this earlier. Nulls are the problem I think. This works as the source of the form:

SELECT *
FROM ContactMasterT
WHERE LastName Is Not Null and Replace(LastName, chr(39), '') Like '*August*'

Adding a similar criteria in your search should work.

 

[raugust]

I'm clearly out of my depth here, but...

The code below does not generate any syntax error messages, but now produces error 13, "Type mismatch". Would not the If conditions prevent null values from reaching the select statement?

Private Sub cmdSearch_Click()
If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."
ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."
Else
'Generate search criteria
GCriterion = "Replace(" & cboSearchField.Value & ", chr(39), '')" & " LIKE '*" & Replace(txtSearchString, "'", "") & "*'"
'Filter Form_PersonlMasterF based on search criteria
Form_PersonalMasterF.RecordSource = "select * from ContactMasterT where " & GCriterion And "GCriterion Not Null"
Form_PersonalMasterF.Caption = "ContactMasterT (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
'Close frmSearch
DoCmd.Close acForm, "SearchF"
MsgBox "Results have been filtered."
End If

End Sub

 

[pbaldy]

You want to test the field, not the variable. Use the technique above to see the finished string and compare it to mine in post 6:

WHERE LastName Is Not Null and Replace(LastName, chr(39), '') Like '*August*'

which says in English "give me everything from the table where the LastName field isn't Null and has a LastName like August, after replacing chr(39)". The Replace() function will barf on Null values, so you need to exclude them.

 

[raugust]

You are right, the problem is the nulls. When I add "NOT (LastName) IS NULL" to the where clause it filters correctly. My problem now is passing the variable name from the search form into the actual filter. I am getting a syntax error from the following code:

Private Sub cmdSearch_Click()
If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."
ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."
Else
'Generate search criterion
GCriterion = "Replace(" & cboSearchField.Value & ", chr(39), '')" & " LIKE '*" & Replace(txtSearchString, "'", "") & "*'"
'Generate null filter criterion
FCriterion = "Not(" & "Replace(" & cboSearchField.Value & ", chr(39), '')" & " IS NULL "
'Filter Form_PersonlMasterF based on search criteria
Form_PersonalMasterF.Caption = "ContactMasterT (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
Form_PersonalMasterF.RecordSource = "select * from ContactMasterT where " & FCriterion & " " & GCriterion
Debug.Print Form_PersonalMasterF.RecordSource
'Close frmSearch
DoCmd.Close acForm, "SearchF"
MsgBox "Results have been filtered."
End If
End Sub

Can you see where my error might be?

 

[pbaldy]

Did you use the technique above to print out the finished string? That's the easiest way to debug these types of things.

 

[spikepl]

Unless you know exactly what you are doing, do NOT use the syntax

Form_PersonalMasterF.SomeControlOnTheFormOrAProperty

because it will get you into deep doodoo when you least expect it.

The proper syntax is

Forms!MyFormname!MyControl

or

Forms!MyFormName.MyProperty

 

[raugust]

Paul - Debug.Print produces no output in this case.

 

[pbaldy]

Get it before it fails:

Debug.Print "select * from ContactMasterT where " & FCriterion & " " & GCriterion

 

[raugust]

The syntax error was a missing close bracket in FCriterion, making the filter contents:

select * from ContactMasterT where Not(Replace(LastName, chr(39), '')) IS NULL AND Replace(LastName, chr(39), '') LIKE '*August*'

... but the type mismatch error is back. If it is Replace that is choking on the null values, is it possible I can't use Replace to exclude null values in the filter specification?


[I did fix the Forms syntax - thanks spikepl]

 

[pbaldy]

Again, you want to test the field, not values.

FCriterion = cboSearchField.Value & " IS NOT NULL "

Presuming you don't have inadvisable spaces or symbols in your field names.

 

[raugust]

That's got it, thanks Paul.

 

[pbaldy]

No problem.