Union Query with select condition

[weckels]

In my union query I want to select * from up to three select queries depending on checkboxes on a form. I always want my union query to include SelectQuery1. If the first checkbox on the form is checked, I also want my union query to include the data from SelectQuery2 and if the first and second checkboxes are checked, I want my union query to include the data from SelectQuery2 and SelectQuery3. Here is what I have:

select * from [SelectQuery1]
union all select * from [SelectQuery2]
union all select * from [SelectQuery3];

This will, of course, make a query with the data from all three select queries. What code can I add to make the second and third line conditional on the checkboxes on the form? Thank you very much in advance!

Will

 

[mikey69]

Suggest you concatenate a SQL string, using if clauses to concatenate conditionally, then run the string.

 

[weckels]

mikey69,
Thanks for your reply. I have tried using if (iif?) many different ways and I always get an error, usually syntax or missing operator. Could you please be more specific? Sorry, but I'm not very knowledgeable about SQL statements.

Thanks again,

Will

 

[mikey69]

You don't use Iif in this situation. What you are trying to do is construct a string. They usually look something like this: (The quotes are important)

String1 = "QueryFieldName1 = " & Me!ControlName1. But there are lots of variations. The Me operator refers to the current form.

When I said concatenate you would then construct a second string like:

String2 = " and QueryFieldName2 = " & Me!ControlName2

Then you can concatenate the two:

Fullstring = String1 & String2

This technique is as flexible as your imagination, but it depends on your particular needs.

Hope you come right.

 

[weckels]

mikey69,
Thanks a lot for the help. I got it to work fine. I appreciate the help!

Will