Unanswered: Union Query and table field format question
Hello, I am trying to use a query to display all the records from two tables.
Both tables have the fields -RefNumber, FirstName, Surname
The RefNumber field in each table has a specific format set for each table -
Table1 = "NWC"000000
Table2 = "CL"000000
So each RefNumber field displays in each table as say NWC000035 or CL000012 and so on.
I have tried a Union query to combine both tables, and while this works and shows the correct amount of fields, it does not display either "NWC000" or "CL000) at the start of the RefNumber table, it only shows say 35, or 12.
How do I get the format prefix to display in the union query?
You will need code similar to this using the format function
SELECT "NWC" & Format([refnumber],"000000") AS ref_number, table1.results
union SELECT "CL" & Format([refnumber],"000000") AS ref_number, table2.results
In the first select NWC is added to the refnumber to give NWC1, the format pads the number with zeros to six digits.
The second select is the same as the first but CL is used in lieu NWC