Create search form with multiple fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Ok, so I read this link http://allenbrowne.com/ser-62.html and I have an idea
of how this might work, but I want to know if there is any other way.

My goal is to build a form that will search a table (or query) for any
combination of the fields available on the table/query, and on the fields,
each text field should be a 'contains' query.

For example, I want to be able to search any LenderID that starts with 8100
and where the name contains "New York".

The code from Allen Browne works, but I want to know if anyone has come up
with any other ways (after this database is built, it may not have support
for some time, so 'less is more' applies to this project).

Thanks!
 
Robert_L_Ross said:
Ok, so I read this link http://allenbrowne.com/ser-62.html and I have an idea
of how this might work, but I want to know if there is any other way.

My goal is to build a form that will search a table (or query) for any
combination of the fields available on the table/query, and on the fields,
each text field should be a 'contains' query.

For example, I want to be able to search any LenderID that starts with 8100
and where the name contains "New York".

The code from Allen Browne works, but I want to know if anyone has come up
with any other ways (after this database is built, it may not have support
for some time, so 'less is more' applies to this project).

Thanks!
Click to expand...


Hi Robert,

If there are as many search fields as there are fields in table, then
you can create query and filter data in record source query of subform
with

Like "*" & Forms!frmFormName!FieldName & "*"

Add this criteria for all fields you are searching.

If you have, for example, 2 search fields to search all fields (say 4
fields) in table, then you could join all fields into one in record
source query:

SearchField: Field1 & Field2 & Field3 & Field4

and add filter:

Like "*" & Forms!frmFormName!FieldName & "*" OR Like "*" & Forms!
frmFormName!FieldName2 & "*"

For 'and' search use

Like "*" & Forms!frmFormName!FieldName & "*" AND Like "*" & Forms!
frmFormName!FieldName2 & "*"

Regards,
Branislav Mihaljev
 
Back
Top