Multi field Search form with VBA

[Patti]

Hello,

I’ve created an employee search form (USA Employee Search) with a record source based on a query (USASearch2). My form has the following searchable fields;
· On-site Redeployment (txtonsite) Yes/No check box
· Off-site Redeployment (txtoffsite) Yes/No check box
· Talent Programme (txtprogramme) Yes/No check box
· On Assignment (txtassign) Yes/No check box
· Mobility (txtmobility) Yes/No check box
· Second Place (txtsecond) Yes/No check box
· Corporate Level (txtlevel) unbound combo box
· Skills (txtskills) unbound text box
· Department (txtdepartment) unbound text box
· Capability (txtcapability) unbound combo box

I’m able to make my form work by using LIKE “*”&[FORMS]![USA Employee Search]![txtonsite]&”*” for the first eight fields, however I believe I exceed the 255 character capacity when I add the last two fields. As the search function no longer works.

I’ve been trying to research and add some VBA code to run this search, however this is a bit beyond my level of understanding and I get lost in the strings. I would like to be able to search by all the fields (including a combination of the fields ie skills and Department).

Any suggestions would be greatly appreciated.

Thanks
Renee

 

[RuralGuy]

Are you creating a dynamic query to do your search or have you just created a static query that tries to capture all of the arguments?

 

[Galaxiom]

I’m able to make my form work by using LIKE “*”&[FORMS]![USA Employee Search]![txtonsite]&”*” for the first eight fields, however I believe I exceed the 255 character capacity when I add the last two fields.

What 255 character capacity is that?

Skills (txtskills) unbound text box
· Department (txtdepartment) unbound text box

Do you have Skills and Departments with the same names and need to search for both at the same time?

Moreover, by recording this data as free text you will often miss what you are looking for due to variations and errors in spelling, abbreviations etc.

 

[Patti]

Static I think. I used the Query wizard to create the query then created my form from the query.

 

[RuralGuy]

You could create a dynamic query in code with a Select Case structure for the WHERE Clause that would not have any practical limit. This link sort of touches on this subject:
http://www.baldyweb.com/BuildSQL2.htm