Hi everyone
I am trying to find a way to write a code that would let me search with 20 different fields(in combination with each other).  The
only way i can think of is trying to include all the probabilities/possibilities/combinations but that becomes too clumsy since
there are over 400  probabilities or combinations that can come up with 20 different fields.  If anyone knows any other way of doing
the search please let me know.  MAIN THING to keep in mind is that i should be able to search records in combination with all 20
fields.
All ideas are highly appreciated


 


Tired of spam? Get  advanced junk mail protection with MSN 8.

just build up the WHERE clause in parts :

SQLStatement="SELECT * FROM myTable WHERE "
SQLTail = ""

if len(optionOne)>0 then
         SQLTail=SQLTail & " AND optionOne='" & optionOne & "'"
end if
if len(optionTwo)>0 then
         SQLTail=SQLTail & " AND optionTWO='" & optionTWO & "'"
end if

amend the AND's to OR's if required and then just trim off the first few 
chars and then add it onto SQLStatement to get the complete SQL string.


At 22:56 17/03/03 +0000, you wrote:


>Hi everyone
>I am trying to find a way to write a code that would let me search with 20 
>different fields(in combination with each other).  The only way i can 
>think of is trying to include all the 
>probabilities/possibilities/combinations but that becomes too clumsy since 
>there are over 400  probabilities or combinations that can come up with 20 
>different fields.  If anyone knows any other way of doing the search 
>please let me know.  MAIN THING to keep in mind is that i should be able 
>to search records in combination with all 20 fields.
>All ideas are highly appreciated
>
>
>
>
>
>Tired of spam? Get  advanced junk mail protection with MSN 8.
>

Maybe i didn't understand it accurately what u wrote in ur email.  From what i understood from the eg u wrote i can only search the
fields individually not in combination with each other.  eg if i have city=edm, #ofadults=2 #ofchildren=3,#prov="AB" then acc to ur
code it will show me all the records for city=edm, all records that match#of adults =2 , all records that match #of children 
etc...?!
but what i want is all combinations like if i enter all the above search criteria, i should be able to search:
All the records that have 2 adults and 2children, and whose province is AB and city is Edm
NOT as 2 adults or 2 children or province=ab or city =edm
I am sorry i didn't quite understand , if you have an eg i can look at that will be great.
Thanks
Protect your PC -  Click here  for McAfee.com VirusScan Online