Concatenating Strings

[Debbie]

I'm trying to put a concatenated field in a report with
data that is stored in a table with a lookup wizard.
Although I get the concatenation to work it returns the
numerical value stored in the table not the text value
that appears when you view the table.

For example I have a list of items...
1=tables
2=chairs
3=lamps
when I concatenate a field to show "tables, chairs, lamps"
it returns "1, 2, 3" instead. How do I get the
concatenated field to show the text value rather than the
numeric.

 

[Debbie]

Thanks for your reply. I guess I didn't explain myself
well enough. I realize the field is a combo box that
saved the number value rather than the text. However,
what I was looking for was how to get the text value from
the other table to appear, such as doing a query or maybe
a sub report. Everything I tried failed... until I
finally recreated the Lookup fields in the table. I
unchecked the "Hide Key Column" and was able to tell
Access to save the text value rather than the numeric.
(When the "Hide Key Column" is checked it defaults to
saving the numeric value.)

Of course after recreating the table fields I also had to
update all the queries and forms where the properties
remained from the old fields as well as the numeric value
that was already stored in the table. Also I found that
before you start the Lookup Wizard you should change any
of the field properties (such as the field length),
because after the Lookup Wizard is done you are not
permitted to change several of the field properties. I
hope I've re-explained myself, and how I finally figured a
work around to resolve the issue. Maybe there would have
been an easier way to resolve this issue... maybe I
wouldn't have this problem if I upgraded from Office
2000... but my guess is I'll never know.

-----Original Message-----
The text showing in the field when you look at the table probably isn't in the table.
Access can make the field look as a combo box would look,

where the value of another table

linked on that field is actually what is showing. With

the table in design view, click on

the field in question and then click on the Lookup tab at the bottom of the window. Has
Display Control been set for Combo Box? If this is what is happening then you actually
need to get the text value from the other table where the

linking value (in this case the

 

[Larry Linson]

Lookup fields are, in fact, a violation of good relational database design
practices, and obscure what is really stored in the table (as you have
discovered). My observation is that rather than simplify using Access, we
get so many questions about problems that I am convinced they complicate the
user's life and are better ignored and avoided. I suspect they were added
because of vocal complaints from "refugees from not-really-relational
database management systems" who thought they were a good idea.

My advice is to simply store the numeric value, and use a query to join it
to the related table when you need the text value.

Larry Linson
Microsoft Access MVP