Optional Multiple Criteria for SQL String

[Markb384]

Hi guys and gals



I have a form that I want the user to specify some search parameters on, 8 fields in total. The user doesn't have to enter all of the fields as long as they complete at least one (although doesn't matter which), but they could enter as many as they like to narrow the results down. For instance, the user could search for all results with a particular Forename, or all results with a particular Date of Birth, or all results with a certain Surname and a certain Address etc.

I am writing the SQL code in VBA, as I thought this was the best way to go and I have done this for some simpler queries before. The variables used for populating the SQL are previously checked to see if the relevant data entry control is complete and to save the value if so, otherwise they remain null (I think).

Is there a way to structure a WHERE clause to skip comparisons if the data is null?

In previous constructions, I have known how many criteria I would have, so I could manually add in AND in a string, but obviously in this case I could have anything from 1 to 8 potential criteria, so I can't manually add in AND.

Any ideas?

Thank you

EDIT: I may have solved this by creating a new "criteriaString" which as the various search parameters are tested to see if the user filled them in, checks if it is a nullString and appends "AND" if it isn't, before appending the criteria itself.

If anyone has a better way though I'd be happy to look at it.

[JoeM]

The way I typically handle this in VBA is to have a separate IF block for each criteria, checking to see if it is Null, and if it is not, add the Criteria to my WHERE clause and add an AND on the end. Each one is done in this manner.

Then, at the end, I simply check to see if length of the criteria string I built is greater than zero, and if it is, remove the last "AND". Than add that criteria string on to my SQL string, then apply the whole SQL string to my Query via a QueryDef.