So I’m here at work trying to create mailing labels for some of our agents in MS Access… Everything is easy enough, however, when inputting agents addresses into the database, I’ve got a Lookup Wizard for the State in which they reside…
Consequently, on the mailing labels everything prints out okay except the state. Instead of NE for Nebraska or IA for Iowa, it’s listing the primary key of 1, or 2.
I’m obviously not an expert at Access. Anybody know what I need to do to get the labels to properly display the State?
I wish I had my Access loaded on this machine. Open the table in question in the design view. (If you open the table, and then click on the green protractor looking thing on the menubar, this should put you there). Click on the row for the State Lookup. Below is a list of properties. There should be a second tab, called “Lookup”. Click in that tab, and I believe (working from memory here) that there should be something called “Lookup Column”. Sounds like it’s currently set at [column]1 instead of 2.
Well, in Access 2003, I open the Agent Table in Design View, and in the State Row (where I set up the lookup wizard for State) the data type is Number and at the bottom I click on the 2nd tab as you mention – the Lookup Tab, and listed there under the lookup tab is:
Display Control
Row Source Type
Row Source
Bound Column
Column Count
Column Heads
Column Widths
List Rows
List Width
Limit To List
I tried changing the Bound Column from 1 to 2, but that did not change anything (I created labels with it both at 1 and 2)… hmmmm… any other ideas? I appreciate the input… I think I’m close.
OK, the combo box you describe is set up to display a lists of states, but it actually stores a number instead, right? Changing the bound colum now will only affect future inputted records, it won’t change what’s already in there.
What you need to do now is create a query based on your main table and your smaller table that lists the relation ship between “1,2” and “NE, MA”. Then use the resulting query as the basis for your labels.
Does that help, or do you need more detailed instructions?
I’m thinking that if he’s printing the labels from Access reports, he can just add in the table containing the States data, then select the field containing the actual text from there instead of from the table with the lookup. Because it’s defined as a lookup, it will already have the necessary relationship to work…
OK. Save a separate copy of the database before trying thsi, so you can restore if it doesn’t work. Seriously.
Go the lookup tab as described before. Change the settings as follows:
Bound Column 1
Column Count 1
For Row Source, click on the field, then on the button to the right with the “…” on it. That will bring up a query builder. Add a field to the query consisting only of the state abbreviation. Close the query. Save the table, and load it up again. Hopefully, that will changed all the 1,2,3 to the abbreviations.
If so, thank me and go about your business. If not, restore your backup and post what happened.
Thanks TellmeI’mNotCrazy, reading this when I got to work today, it dawned on me that I just need to make a query (with all the mailing label information) while using the state abbreviation from the state table instead of the state listed in the agent table… then make the mailing labels with the respective query. Works great.
JSexton, I’m going to experiment with your idea now, 'cause want to see what happens!
Glad you got it worked out. Your approach is a more efficient approach to my method (mine was just building the same query inside the report) but wasn’t sure how comfortable you were with actual query building. So kudos to you!