Form Filter with like

[andy1968]

I'm trying to filter a subform based on a value on the main form.


I'm filtering on 2 fields.


One is Job.


The for the second field, I want all the records that have the same characters prior to the 1st "R" in the string.


Here is the code.

If InStr(Me.ContractorNumber, "R") > 0 Then
   Dim CN as String

   CN = Left(Me.ContractorNumber, InStr(Me.ContractorNumber, "R") - 1)
   
    Me.frmSubmittalEntryResubmittalList.Form.filter = "[Job] ='" & Me.Job & "' And  [ContractorNumber] Like '*' '" & CN & "'"
    Me.frmSubmittalEntryResubmittalList.Visible = True
    Me.frmSubmittalEntryResubmittalList.Form.FilterOn = True
End If

I get a run time error '3075': Syntax error (missing operator) in query expression '[Job] ='C18231F' And [ContractorNumber Like '*' 12"

 

[theDBguy]

Hi. Maybe you meant something like:

...And  [ContractorNumber] Like '" & CN & "*'"

 

[arnelgp]

...
...
... And [ContractorNumber] Like '*" & CN & "*'"

 

[andy1968]

Thank arnelgp!


Work like a charm.

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[arnelgp]

you're welcome, sir!