Calulated Text Field Using Concatenated Test Lookup Fields

[kathyb]

Hello,



This is my first post and I am sure an easy question.

I am setting up a golf club membership database in Access 10.

I have created a Roster table which includes member information field including Address, City, State and Zip among others. The City and State fields are populated via separate lookup tables (CiityLookup and StateLookup, respectively). These two fields in the Roster table have been populated, without incident, using combo list containing the following values– City – Carson City, Gardneville, Markleeville, Minden… - State – CA, NV. I then created a calculated field in the in the Roster table using the following Expression syntax – [City] & ", " & [State] & " " & [Zip]. An Example of the expected result would be Minden, NV 89423, however the actual result is 4, 2 89423. The 4 and 2 representing the numeric location of the value in the corresponding lists where 4 = Minden, 2 = NV. I have checked and played with relationships, data types, etc. and for the life of me cannot figure this out.

Any help would be greatly appreciated.

Thanks in advance
kathyb

[June7]

That's because the actual values in the Roster table City and State fields are the ID values from the lookup tables. Review http://access.mvps.org/access/lookupfields.htm

To retrieve the city and state names, build query that joins the tables.