Match Criteria or display All Records

[Guest]

Hey Sharon -

To handle the null records put the forms!searchFrm!
SearchBox on the field line of a new column in your query
and then in the criteria line put Is Null on an or line.

The SQL would be something like;
SELECT Table1.*
FROM Table1
WHERE (((Table.FieldToSearch) Like "*" & [Forms]!
[searchform]![seachfield] & "*")) OR ((([Forms]!
[searchform]![seachfield]) Is Null));

To do multiple keywords search is more complicated. When
you say they want to search for >1 keyword do you mean
they want to return any record that has at least one of
the keywords they enter? OR do you mean return any record
that has ALL of the keywords they enter?

- Anne

 

[Guest]

Thanks Anne,

I don't how it worked, but it did!

I think they want to do an OR multiple search and it's really causing me
grief. I have one field called Keyword in the Customer Table, the idea is
that they will separate with commas (text data type 255 chars).

They want to input say nine search words and find all companies that meet
any one of those criteria.

Any ideas?

Sharon

Hey Sharon -

To handle the null records put the forms!searchFrm!
SearchBox on the field line of a new column in your query
and then in the criteria line put Is Null on an or line.

The SQL would be something like;
SELECT Table1.*
FROM Table1
WHERE (((Table.FieldToSearch) Like "*" & [Forms]!
[searchform]![seachfield] & "*")) OR ((([Forms]!
[searchform]![seachfield]) Is Null));

To do multiple keywords search is more complicated. When
you say they want to search for >1 keyword do you mean
they want to return any record that has at least one of
the keywords they enter? OR do you mean return any record
that has ALL of the keywords they enter?

- Anne

-----Original Message-----
I am trying to build a query which will allow me to search multiple fields
via a form. The majority of it works OK, but when I try to search on the
'Keywords' field; I only see those records who are not null. Not all records
will have an entry in this field. If I don't enter any criteria, I don't get
any records!

How can I build the query to say "If the Forms!SearchFrm! SearchBox is Null,
display all records, else display records that match SearchBox".

Currently the query states LIKE"*" & forms! searchFrm!SearchBox &"*"

Also, they want to be able to search for a few keywords at one time. Can it
be done?