By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,994 Members | 2,073 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,994 IT Pros & Developers. It's quick & easy.

Like operator with unbound text boxes

P: 25
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2. DoCmd.ApplyFilter "", _
  3.        "([RecordNumber] = [Forms]![frmJobSearch]![txtRecordNumber] " & _
  4.       "Or IsNull([Forms]![frmJobSearch]![txtRecordNumber]))" & _
  5.    "And ([JobName] Like [Forms]![frmJobSearch]![txtJobName] " & _
  6.       "Or IsNull([Forms]![frmJobSearch]![txtJobName]))" & _
  7.    "And ([ContractorsBidding] Like [Forms]![frmJobSearch]![txtContractor] " & _
  8.       "Or IsNull([Forms]![frmJobSearch]![txtContractor]))", ""
  9. End Sub
Apr 3 '16 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,347
Check out Example Filtering on a Form.
Apr 3 '16 #2

P: 25
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?
Apr 4 '16 #3

NeoPa
Expert Mod 15k+
P: 31,347
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.
Apr 4 '16 #4

P: 25
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:
Expand|Select|Wrap|Line Numbers
  1. "And ([JobName] Like "*" & [Forms]![frmJobSearch]![txtJobName]  " & "*" & _
  2.       "Or IsNull([Forms]![frmJobSearch]![txtJobName]))" & _
Apr 5 '16 #5

NeoPa
Expert Mod 15k+
P: 31,347
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.
Apr 5 '16 #6

NeoPa
Expert Mod 15k+
P: 31,347
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.
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. AND ([JobName] Like '*' & [Forms]![frmJobSearch]![txtJobName] & '*')
Apr 5 '16 #7

Post your reply

Sign in to post your reply or Sign up for a free account.