Form Expression showing Primary Key numbers

[CheckHound]

I am using a Report to make generate a letter. The fields I am trying to display are all generate from lookup fields. If I add each control individually they display correctly. If I try to have them display as one line by using an expression it displays the number of the primary key for the data and not the correct word.

This code generates 1,1,1 with no errors, the number change depending on the record.
=[City] & "," & [State] & "," & [Zip Code]

My question is how do I get the expression to display the actual data. Like Couer d'Alene, Idaho, 83815 ?

Heather

 

[MacCaillean]

Hi Heather

I'm guessing you set the lookups in your table design, and when you place the controls individually, you get combos which display the meaningful lookup value rather than the ID number. When you combine the fields into a single unbound control, however, you're using a textbox, which displays the ID number rather than the meaningful value.

If you have built tables for all the city, state and zip values you need, then:

1) create a query showing these 3 tables along with your addresses table;
2) add in all the fields from your addresses table, except those which correspond to city, state and zip;
3) for these, add in the meaningful value from the appropriate table, e.g. CityName and not CityID from the Cities table;
4) run the query to make sure the right values are displayed;
5) add your combined field (remember to put spaces after the commas so your data is separated) to the report as you were doing before.

Bingo - that should do it!

Hope this helps.

 

[MacCaillean]

Sorry - I should have mentioned that you need to make sure your report is now based on the query you've built.

See ya!