Like & "*" with no results

[Timothy]

I have a query by form with multiple entry parameters
using a is not null and like & "*"form!formname!entry
& "*" in the query for each parameter. My problem is if I
leave one entry blank I retrieve all records, because of
the *'s.

I need the correct statement, still using the like
& "*"...&"*", to say if one entry is left blank then
return NO records. So only the entries that have a
critera entered will return a record.

Any and all help is greatly appreciated.

Timothy

 

[Wayne Morgan]

like & "*"form!formname!entry & "*"

Like "*" & form!formname!entry & "*"

 

[Michel Walsh]

Hi,



Should be

LIKE "*" & FORMS! ...


not

LIKE & "*" FORMS! ...


Since you repeated it twice, I assume it is indeed a typo in your original
query.... and surprised you didn't get an error of syntax. If not, please
post the exact SQL text that had been generated.


Hoping it may help,
Vanderghast, Access MVP

 

[timothy]

your right it was a typo but the question still remains. I
have a is not null and like "*" & form!name!entry & "*"
that works but i need to add another entry box to narrow
the search, but if nothing is entered in the second box i
dont want all the records returned.

I have a table with 15 fields that are all being searched
with this same string, so I would like to be able to
narrow the search...IE find BOB, and RED CAR, and BUICK
all of which have thier own field. so the query would
return only the record that has all the matching critera.

Please help, bosses are getting ancy...

Timothy

 

[John Spencer (MVP)]

You might try

Like "*" + FORMS!FormName!ControlName + "*"

Note the replacement of the & with the + sign.

This may give you an error. If so, try

Like "*" &
NZ(FORMS!FormName!ControlName,"SomeImpossibleValueThatShouldNeverOccur")
& "*"

 

[timothy]

That works great, but It doesnt seem to narrow the search.
It will search for 2 things at once. Ive tried using a AND
between the two, but if I leave the 2nd box blank the
first criteria is not found. is there a conditional
statement that can go in there? if so what is it.
You all have been very helpful. Im starting to understand
the syntax...finally.

Timothy

 

[John Spencer (MVP)]

OK, it seems as if you may be getting into the territory of building the SQL
using VBA or I am not understanding your requirement. The following MAY work
for you.

You want to search for records where
FieldA contains "Red" AND FieldB contains "Tomcat" AND FieldC contains "Art"
But if any one is left blank then you want to IGNORE that field and return
records just based on the other two criteria. Is that correct?

Assuming that is correct, the SQL could look something like:

SELECT * FROM SomeTable
WHERE (FieldA Like "*" & NZ(FORMS!FormName!ControlNameA,"XXX") & "*" Or
FORMS!FormName!ControlNameA is Null)
AND (FieldB Like "*" & NZ(FORMS!FormName!ControlNameB,"XXX") & "*" Or
FORMS!FormName!ControlNameB is Null)
AND (FieldC Like "*" & NZ(FORMS!FormName!ControlNameC,"XXX") & "*" Or
FORMS!FormName!ControlNameC is Null)

That will get rearranged by Access and if you have many criteria this will get
too complex.

In the query grid you would need to enter each set of criteria under the field
in ONE criteria cell. Something like:

Field: FieldA
Criteria: FieldA Like "*" & NZ(FORMS!FormName!ControlNameA,"XXX") & "*" Or
FORMS!FormName!ControlNameA is Null

The above criteria would all be on one line (except that newsreader will
probably wrap it to two or more lines.

 

[John Spencer (MVP)]

OK, the technique I suggested is GOING TO BREAK with 40 fields. To do what you
seem to require means that the SQL string be built using VBA (at least to me).
It is beyond my free time to do this in the newsgroup. You might try looking in
the Microsoft Knowledge Base for a technique to build the SQL string (especially
the where clause).

Also, you might be able to satisify the requirement using the Query by Form
method that is already built-in.

As to your earlier question, yes "XXX" was just shorthand for "someValueThatWillNeverExist".