Search field (loop through 10 fields in query)

[HansD]

Hey everybody

I 've got another question:

I have a form with a subform (based on a query) full of addresses
I am trying to make a advanced search.
I've got the simple searches (for examplelace, postalcode, between postalcodes) working.

But here is my problem:

every address has 10 selectioncodes (selectioncode1, selectioncode2, ....) these are comboboxes with a word(like: VIP or Theater or employees) So, selectioncode1 could be VIP and selectioncode2 could be Theater or the other way around.

on my searchform you can select 3 selectioncodes (Src_Selectioncode1, Src_Selectioncode2 and Src_Selectioncode3).

When somebody selects a code in Src_selectioncode1 i want it to search through the query in all the selectioncode fields and show the results in the subform. you can also give 2 or 3 selectioncodes and then I want it to do the same

I really hope somebody understands my question because it is giving me headaches

 

[DougP]

'Well you have to build a SQL statement on the fly
'this is done something like so

Dim MyCriteria, MySQL As String
MySQL = "Select * From [yourTable] Where "
MyCriteria = ""
'add the 10 check boxes to your form so if they are checked the following code will add to the above SQL
If Check1.Value = True Then
MyCriteria = MyCriteria & "selectioncode1 = True AND "
End If
If Check2.Value = True Then
MyCriteria = MyCriteria & "selectioncode2 = True AND "
End If
If Check3.Value = True Then
MyCriteria = MyCriteria & "selectioncode3 = True AND "
End If
'etc
'etc


'Then see if the last 5 characters are " AND " and trim them off
If Right(MyCriteria, 5) = " AND " Then
MyCriteria = Left(MyCriteria, Len(MyCriteria) - 5)
End If

MySQL = MySQL & MyCriteria & ";"
Debug.Print MySQL
--------------

DougP, MCP

 

[HansD]

Hey DougP

Thanks for your reply.

You really helped me on the way
I have it to work now
Thanks man

Hans