Labels are printing the wrong field

[joshbula]

It has several tables joined together. On the main table, I have a field named “Band” that is joined to a table named “Bands”. I did it by selecting the “lookup wizard” in the field properties. So, for the “band” field in the main table, it looks up values in the “Bands” table and has a pull-down box to select “Symphonic Band,” “Concert Band,” “Jazz Band,” etc.

The problem is when I’m printing labels. Instead of printing the band name, it’s printing the primary key. For example when it should print “Symphonic Band” on the label, it actually just prints “2”

The code inside the text box in the report designer is =Trim([Band] & " " & [Instrument]). But instead of printing “Symphonic Band Flute” it’s printing “3 Flute” where 3 is the primary key field from the “Bands” table.

Removing the separate primary key field is not an option because that is what it uses to sort. (it's not sorted alphabetically, but rather by the grade level of the bands.)

I know in SQL you can add the table name to the field name, so I’ve tried changing it to [Bands.Band] but it didn’t work. Is there some way to modify that “Trim” command so that it displays the data from the correct field on the labels? I don't have this problem with any other text areas on any reports, only when the text areas are concactenated using the "=trim" thing.

Thanks,
....Josh

 

[Remou]

Ah ... I think that comes with a warning (similar to cigarettes):

http://www.mvps.org/access/lookupfields.htm

You should probably base your labels on a query.

 

[joshbula]

Oh my, I wish I knew that earlier. Is there a better way to accomplish the same thing?

I actually am basing the labels on a query, but it still isn't working.

Any ideas?
Thanks,
...Josh

 

[Remou]

I do not think there is any problem with a similar sort of 'lookup' on a form. Also, make sure all your relationships are set up. Here are a few links, gathered from various posts, that you may wish to look at:

http://www.alphabetcitydataworks.com/fundamentals.htm

http://r937.com/relational.html

(the two links above are the same article in different formats)
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

This query (SQL view) may give you some ideas:

SELECT tblMain.MainID, tblMain.BandID, tblBand.[Band]
FROM tblMain INNER JOIN tblBand ON tblMain.BandID = tblBand.BandID;