Getting ID’s rather than values I want

[David Ball]

Hi,

I have a query with fields Make, Model and Colour which have the values provided by a Combobox drop down in the source table.
I want to include a calculated field in the query that concatenates these together to give a description of the vehicle.
Vehicle: =[Colour]&” “&[Make]&” “&[Model]
The problem is this returns the ID’s and I get something like “1 1 3” rather than “Red Ford Mustang”.
I tried Vehicle: =[Colour].[Column](1)&” “&[Make] .[Column](1)&” “&[Model] .[Column](1) but Access rejects it.
Am I able to create such a field in my query?
Thanks very much
Dave

 

[JHB]

Have you set it as a Lookup field at the table level?
http://access.mvps.org/access/lookupfields.htm

 

[David Ball]

OK, I gather that is not a good idea.
So I changed my fields in the table to Textboxes and now even the values in the table are showing just ID's!
The form I use to populate the table has comboboxes with drop-down lists so the user can select a value (and I can control what they enter).
How can I show the values I want in the table now?

Thanks very much

 

[JHB]

You need to create a new table with the data you've had in the Lookup field.
Example:

 

[David Ball]

Hi JHB,

I do have tables for each field. This is where the lookups in the main table get there values. Basically, I want all the fields to have there values in individual tables, then have them all bought together in a single table and have that table as the source for the Form where users can select values from Comboboxes. Is this OK?
The problem is I want to concatenate some of the values together in the main table but end up with just the ID's concatenated, not the values.

Thanks

 

[pbaldy]

Apparently continued here:

https://www.access-programmers.co.uk/forums/showthread.php?t=301851

 

[JHB]

Let's if I can explain it for you!
A sample database is attached, open the form in design view.
Mark the combo box and look at the property for it:
Tab "Data": Row-source and Bound-Column
Tab "Format": Column-Count, Column-Widths

Take a look at the query "qryForReport" how you get the textvalue to appear instead the Id's.