How to select values from two columns in one field?

[drsayman]

Hello, I have a form (Orders) with a subform (OrderDetails) that have a fields like a Barcode(from tblBarcode), ProductID(tblProducts), ProductName(tblProducts) and etc.



I want to union Barcode and ProductID fields into one where I can type ProductID.Value or Barcode.Value(if have Barcode) and fill in ProductID.Value in the same field.
Or union three of them BarCode,ProductID,ProductName if can.
Here are some more clarification on the task.
1. I have a tblBarcode with a fields ProductID (text) and Barcode (text).
2. I have a tblProducts-ProductID (text),ProductName(Text) and etc.
3. form OrderDetails with a field ProductID where if you enter "barcode" to write ProductID which refers to the barcode, but I want to can enter and ProductID when barcode missing.

Thank's for a help in advance.

[June7]

First, do a SELECT query that joins the two tables.

Then do a UNION query with fields of the SELECT query. There is no wizard or designer for UNION, must type into the SQL View of query designer. Like:

SELECT ProductID, ProductID As Criteria, ProductName FROM query
UNION SELECT ProductID, Barcode, ProductName FROM query;