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:

 

[Pat Hartman]

While lookups on the table are bad, lookups on forms are good. That means that the forms should use combos rather than text boxes. That will allow them to display the text value but still be bound to the ID.

 

[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

 

[Pat Hartman]

Storing the text strings duplicates data and isn't necessary. It can also lead to invalid results should you change any of the source text strings.

That said, do the concatenation in the query if you must rather than saving the mushed value.

 

[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.