Drop down numbers in concatenated fields

[thrake]

I have a table (table1), some of whose fields are populated by dropdown lists. The data for the dropdown lists are in other tables (table2, table3,...).

. When I run a query, against table1 the normal values appear in the query.
. When I concatenate the fields I get the reference numbers, from table1.

I have run the query with and without concatenation in the same query.

My software is Access 2003.

[Rabbit]

Your first query isn't showing the normal values. The "normal" values is the reference number. Your table is using lookup fields. You see the text that's associated with the numbers but the table is actually storing the number, not what you see.

When you select a field without doing anything to it, it will copy the lookup fields. When you attempt to do a calculation on it, i.e. the concatenation, then the calculated value has no lookup values. It uses the values stored in the table, which is the reference numbers. To concatenate the associated text, you have to link the values back to the lookup tables and concatenate the text, not the fields in the main table.