Like operator with unbound text boxes

[jalbright1957]

I have a continuous form set as a modal type. In the detail section of the form I have the following bound fields: RecordNumber, JobName & ContractorsBidding. The header of the form contains text boxes to accept input from the client to perform a search on any of these three fields. The action button in the header is set to perform a filter to display the requested search. The eventual goal is to allow the user to select the record from the search results and display it in a separate form. I am unable to get the Like operator to display more than a single record and the search criteria has to be entered exactly. When I try to add the "*" & into the code, a type mismatch is generated. This is what I have so far:

Private Sub cmdSearch_Click()
DoCmd.ApplyFilter "", _
       "([RecordNumber] = [Forms]![frmJobSearch]![txtRecordNumber] " & _
      "Or IsNull([Forms]![frmJobSearch]![txtRecordNumber]))" & _
   "And ([JobName] Like [Forms]![frmJobSearch]![txtJobName] " & _
      "Or IsNull([Forms]![frmJobSearch]![txtJobName]))" & _
   "And ([ContractorsBidding] Like [Forms]![frmJobSearch]![txtContractor] " & _
      "Or IsNull([Forms]![frmJobSearch]![txtContractor]))", ""
End Sub

[NeoPa]

Check out Example Filtering on a Form.

[jalbright1957]

Thanks for the input. I'm not 100% with how it works yet but will take a go at it. Would you happen to know why with what I had put together would only show results if for example the full job name was entered into the text box?

[NeoPa]

JalBright:
Would you happen to know why with what I had put together would only show results if for example the full job name was entered into the text box?

Oh yes. That's because you haven't used any wildcard characters.

Using Like with a value without wildcards is equivalent to using Equals.

[jalbright1957]

I thought I had understood that and so didn't think it would be an issue to add a wildcard but when I do, I get a type mismatch error. In attempting to search for anything similar as in this:

"And ([JobName] Like "*" & [Forms]![frmJobSearch]![txtJobName]  " & "*" & _
      "Or IsNull([Forms]![frmJobSearch]![txtJobName]))" & _

[NeoPa]

But that's all wrong. Look at where all the double-quotes (") are in that code. They don't match up, and where they are is not what you want. You can't be sloppy with code. It has to be exactly what you want, or it just won't work.

[NeoPa]

Also, it's much easier to use Nz() than having two distinct comparisons for a single field. In this case, even that is redundant as you can use Null propagation (Using "&" and "+" in WHERE Clause). EG.

AND ([JobName] Like '*'+[Forms]![frmJobSearch]![txtJobName]+'*')

The + operator uses Null propagation so that if either side is Null then the result also is.

As it happens, when I look back, I see that it's even easier than that, as you don't want to use Null propagation. For your requirements you just use the ampersand (&) to create the string and you end up with '**' (IE. All records) if the control is Null :

AND ([JobName] Like '*' & [Forms]![frmJobSearch]![txtJobName] & '*')