Using Access query data lookup values

[Kipi78]

Hi, I have a data base with names and address. The street addresses are
stored in two fields [AddrHouseNum] and [AddrRoadCode] the [AddRoadCode]
field is stored as a three character code, for example Locust Lane is LOC.
When I display the fields separately, they show correctly as i.e. "1234" and
"Locust Lane". When I try to concatenate the the house number and road name
into a query field I get "1234 LOC". My question is how do I get the look-up
field [AddRoadCode], in this case "Locust Lane" to show, rather than the
code "LOC"?

 

[John Spencer]

There must be another table in your database that has the ACTUAL names
associated with the AddrRoadCode. You need to join that table to your current
table on the AddrRoadCode. Once you do so you should be able to use the field
with the actual name.

Your problem stems from the fact that you have set up a field in your table as
a lookup field. The problem with doing this with a FIELD in a table is that
Access will store one value (AddrRoadCode) in the table, but will display
another value (AddrRoadName). This works UNTIL you attempt to do something
more complex than just show the data. If you try to filter or sort the field
you need to do so on the values in AddrRoadCode and not the values in the
associated AddrRoadName (in another table).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County