help with Union Query

[swenger]

I have three tables with some fields that are similar but each table has a few unique fields. I want to select the records which match different criteria for each table. So I added a WHERE line to each table. This query returns the correct results.



SELECT ID, Field1 as [SN], BlueID, HWr, FW
FROM TABLE1
WHERE Field1 <> "UN"
UNION ALL
SELECT ID, Field1, BlueID, HWr, FW
FROM TABLE2
WHERE Field1 <> "NA"
UNION ALL
SELECT ID, Field1, BlueID, HWr, FW
FROM TABLE3
WHERE Field1 is not null
ORDER BY [SN]

Now that I have a combined view off all the records in one query, I created a 3 Combo Boxes on a Form which each displays Field1 from each Table. I have an Option Box which only displays the Combo Box based on the option choice. I set the combobox.visible = false on the other 2 combo boxes.

First I created a Public variable called SNS which gets populated by the results of the Combo Box on the form that I just mentioned.
I also have a Public Function called SNSearch() which populates also by the results of the Combo Box on the form that I just mentioned.

I tried adding both above options to my Union query as follows
WHERE [SN] = SNS; or
WHERE [SN] = SNSearch();

but both give me an error.

As a workaround, I created a second select query based on my union query and simply put SNSearch() in the criteria of the SN field. But is there a way to modify the union query without creating a second query.

Thanks
Sam

[ranman256]

You can't put variables in an Query.
you can use form objects as variables...
select * from table where [field]=forms!myForm!txtBox
or
You CAN assemble it in code THEN run it.....

sSql = "select * from table where [field]='" & sSns & "'"
docmd.runSql sSql

[orange]

Why do you use Union All vs just UNION?

[swenger]

I don't know. That's what I found in the help

[swenger]

Thanks.

My workaround with a second query works so I think I will just keep that.

[orange]

Union ALL will have duplicates if they exist in any of the components of your UNION.
UNION (without ALL) removes any duplicates.