Using like and Or in VB form.filter statement.

[himmelrich]

This works:
Forms![Master Search].Form.Filter = "[GTPO] = Forms![Master Search]!
Text63"
FilterOn = True

This doesn't work: (data type mismatch ERROR)
Forms![Master Search].Form.Filter = "[GTPO] = Forms![Master Search]!
Text63" Or "[Project Name] Like '*" & Forms![Master Search]!Text64 &
"*'"
FilterOn = True

 

[Douglas J. Steele]

The Or needs to be inside the quotes:

Forms![Master Search].Form.Filter = _
"[GTPO] = Forms![Master Search]!Text63 Or " & _
"[Project Name] Like '*" & Forms![Master Search]!Text64 &"*'"
FilterOn = True

 

[S Himmelrich]

Can you recommend a good book so I can learn syntax.....I've
implemented the following, the form doesn't update.


Private Sub Text63_AfterUpdate()
Rem Forms![Master Search].Form.Filter = "[GTPO] = Forms![Master
Search]!Text63" Or "[Project Name] Like '*" & Forms![Master Search]!
Text64 & "*'"

Forms![Master Search].Form.Filter = _
"[GTPO] = Forms![Master Search]!Text63 Or " & _
"[Project Name] Like '*" & Forms![Master Search]!Text64 & "*'"
FilterOn = True

End Sub

 

[S Himmelrich]

thanks for your help.....I think I'm not asking the right question so
let me try my question instead of asking for syntax.

Bascially I have three unbound fields in my header. The user may
choose to use any combination, potentially leaving some blank, which
should translate to all records if left blank.

What is the best approach to doing this. I could build the a QUERY
that addresses this, but I don't know how to have a form use a query
within VB (unless it has a datasheet pop up, which I don't want).

I could use SQL, which I'd pull from the Query as stated above, but I
don't know how to put into SQL.

Doing each of the searchs in separate screens is about the extent of
my skill.....do you know of examples that I may review in
accomplishing the top two?

Thanks for all you help, Scott Himmelrich

 

[Douglas J. Steele]

Use VBA to determine which, if any, fields are filled in and set the filter
accordingly.

Dim strFilter As String

If Len(Forms![Master Search]!Text63 & vbNullString) > 0 Then
strFilter = strFilter & _
"[GTPO] = " & Forms![Master Search]!Text63 & " OR "
End If

If Len(Forms![Master Search]!Text64 & vbNullString) > 0 Then
strFilter = strFilter & _
"[Project Name] Like '*" & _
Forms![Master Search]!Text64 &"*'" & " OR "
End If

If Len(Forms![Master Search]!Text65 & vbNullString) > 0 Then
strFilter = strFilter & _
"[SomeField] = " & Forms![Master Search]!Text65 & " OR "
End If

If Len(strFilter) > 0 Then
' Remove the extraneous " OR " from the end
strFilter = Left(strFilter, Len(strFilter) - 4)
Forms![Master Search].Form.Filter = strFilter
Forms![Master Search].Form.FilterOn = True
Else
Forms![Master Search].Form.Filter = vbNullString
Forms![Master Search].Form.FilterOn = False
End If