Like Statement with dual entry

  • Thread starter Thread starter Timothy
  • Start date Start date
T

Timothy

I've been using the is not null and like '*' & [forms]!
[searchbox]![entry] & '*' to use a form on a query that
searches for text. I would like to add another box on the
form to search for another text input (called [forms]!
[searchbox]![entry1]) or to narrow the search. like search
for: Buicks, and the next box : Red. to narrow the search
for any fields in the table that has red or buick.Ive had
luck getting multiple correct records, but when the second
criteria is blank I get all the records. Anyone know how
to solve this one???
Thank you

Timothy
 
It didnt work. I know that no records will come back if I
didnt have the '*' in the statement, is there a way to get
around the wildcard if there is not something entered in
the field????

thanks
 
Hi,


I misplaced a closing parenthesis,

WHERE (fieldName LIKE '*' & FORMS!FormName!ControlName & '*') AND
( fieldName LIKE '*' & FORMS!FormName!ControlNameColor ) & '*' AND Not
(FieldName IS Null)


should have been

WHERE (fieldName LIKE '*' & FORMS!FormName!ControlName & '*') AND
( fieldName LIKE '*' & FORMS!FormName!ControlNameColor & '*' ) AND Not
(FieldName IS Null)



To avoid a LIKE when you want an = but avoid the problem when the form
control is Null, if no Null value exist in the field, then:


WHERE FieldName = Nz(FORMS!FormName!ControlName, FieldName)


and if a NULL is possible for FieldName, and wish to get those records, you
can try a OR:


WHERE FieldName=FORMS!FormName!ControlName OR
FORMS!FormName!ControlName Is NULL


Be sure to add ( ) if you use further AND clauses, to be safe in the order
of evaluation.




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top