How to create a type of search engine using SQL??? Impossible? 1

[KellyStee]

I have 5 parameters - called 1,2,3,4, and 5 - that match to 5 fields in a table.
I want to return all records where
parameter 1 = field 1 and
paramter 2 = field 2 and
paramter 3 = field 3 and
paramter 4 = field 4 and
paramter 5 = field 5.
The problem is all 5 parameters may not be populated at any one time. The user could populate any combination of the parameters. So if one of the parameters is null I would want to ignore that part of the where clause.

So if a user enters
p_1 = ABC
p_2 = DEF
and p_3 = HJI, I need to find all records containing these values
but if a user enters
p_1 = XKL
p_2 = OOK
p_3 = RRJ
and p_5 = MON, I need to find all records containing these values.
Either this is super easy and I'm missing the boat or this is harder than I thought (maybe impossible?).

BTW, I'm trying to do this in Access.

Please help!
Kelly

 

[swampBoogie]

select * from t
where (<parameter1> is null or <paramter1> = field1)
and (<parameter2> is null or <paramter2> = field2)
and (<parameter3> is null or <paramter3> = field3)
and (<parameter4> is null or <paramter4> = field4)
and (<parameter5> is null or <paramter5> = field5)

 

[KellyStee]

GENIUS!! Thanks!

 

[KellyStee]

Hi, swampBoogie! I have one additional question related to this issue:
I have a user that wants two parameters - one to enter letters that are contained in a field and one to enter letters not contained in the field (they want to move away from the 5 parameters mentioned above).
So they want to be able to enter a non-fixed number of characters in the &quot;in&quot; parameter and also enter a non-fixed number of characters in the &quot;not in&quot; parameter and pull all codes with the &quot;in&quot; characters that do not contain characters from the &quot;not in&quot; parameter.
If I know they are always entering 5 characters (or up to 5) it seems I know how to code that. The problem is if they enter more than 5 characters. . .
It seems I would need to write some type of loop to do this? I've never done that before and not sure if I can do it in Access. Any other ideas? I'm wondering if I would only accomplish this through a combination of VB and SQL?

Thanks again for the help!
Kelly