SQL Query based on combination of multiple Fields (1 Viewer)

[wsaccess]

Hi,

I have a form with 6 combo boxes in it. I have a command button, which should display the query results when clicked. Now, my challenge is to build the query based on the 6 different fields.

ie. query should be:

WHERE Field1 = "Value" AND Field2 = "Value" AND FIELD3 = "Value" AND FIELD4 = "Value" AND FIELD5 = "Value" AND FIELD6 = "Value"

Now, if the user chooses to select only 2 of the combo boxes (the rest 4 combo boxes are null), then the query should be based on those 2 only

WHERE Field1 = "Value" AND Field2 = "Value"

and so on.

Basically, I need to query based on the combination of combo boxes selected.

Is this possible? Is there an easy way of doing this?

Thanks
WS

 

[Minty]

I would have a look here ; http://allenbrowne.com/ser-62.html it should get you into a good space.

 

[wsaccess]

Thanks! let me go though it now

 

[wsaccess]

Hi Minty,

That link was quite helpful. Now I got the logic to work.

However, I have another small problem.
When I click "Filter" (as shown in the sample database), I am opening a report.

DoCmd.OpenReport "rptTest", acViewReport, , , , strFilter

Although strFilter has the right WHERE condition, the report doesn't filter itself. It shows all the records.
But, when I open the report in design view, type the strFilter in the "Filter" it works perfectly fine.

Any idea???


Thanks in advance
WS

 

[wsaccess]

I was being so careless!! :banghead:

I was giving the "strFilter" as the wrong argument while opening report

This worked:

DoCmd.OpenReport "rptTest", acViewReport, , strFilter

 

[Minty]

Glad you sorted it out.