Filter using combo boxes that are not null

[aliendan]

I want to apply filter criteria to a query using VBA what the user chooses from any selected combo boxes. The boxes on the form are: County, Block, Section, Well #, and Owner from a tale called tblWllInfo. If the user chooses criteria from County and Section...that would be the filter or if the user chooses Block and Owner then that would be the filter. I'm trying to get away from writing seperate SQL for each instance. I would like to use only one SQL statement for the operation. Can anyone help me out here? [sig][/sig]

 

[kathryn]

Your code needs to look something like this


dim strSQL as string


strSQL= "SElect....... WHERE"
if not(isnull(me!cboYourCountyComboboxName)) then
strsql= strsql & " County = " & me!cboYourCountyComboboxName & " AND "
end if
if not(isnull(me!cboYourBlockComboboxName)) then
strsql= strsql & " Block = " & me!cboYourBlockComboboxName & " AND "
end if

etc....


You will need to examine your SQL to make sure you built it correctly and you will have to get rid of the final AND, but that should get you started in the right direction. [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[aliendan]

Yes, that's it! I wasn't removing the ()'s placed there by the query builder. Once I removed the ()'s the errors quit and it all worked just great. Thank you, Kathryn, for clearing my head. [sig][/sig]