I'm trying to create address labels from a query.

One of the returned fields is 'title', as in Mr., Mrs, etc. The display control for this field is a combo box with row source a table called 'Titles' with numeric values 1-7 and text titles Mr, Mrs etc.

When I run the query, the Title field values returned are in text form, which is what I need, Mr, Mrs etc. If I base a report on the query using the report wizard, I get text values too. But if I use the Label Wizard to generate a report from the query, instead of the text values for the 'Title' field, i get the numeric values, eg '1' for 'Mr.'. The same happens if I use Office links and Word to generate labels.

What can be going on?

I'm a noob at this, as you can guess. Thanks for your help.

Bartok

Are you using lookup fields in your tables? It sure sounds like it and that is one drawback to doing so. You should be using forms to enter data (you can have combo boxes which store the actual ID numbers but then use queries as your report recordsource to display it the way you want). If you use lookup fields, the data LOOKS like it is storing the text, but really is storing the number like it should, but you have a harder time displaying the text when you could just link in a query.

I didn't realise the lookups were exactly what they say. That's the problem I guess.

I am using a form and combo box to enter data. The thing that puzzles me is why a straight report based on a query looks right (giving me the text values), when an address label report based on the same query gives me the numerical values.

thanks for your help, bob.

Bartok

Does this mean that the combo box should be storing its column 1 (display value) value in the main table, rather than its column 0?

The Combo Box Wizard offers to store the selected value in the database, but the column it stores is column 0.

How do I change the behaviour of the Combo box to store column 1? If I could do this, would it should solve the report problem?

Alternatively, how would I get a new field 'titletext' in the main table populated with the appropriate text values, so I could use those for labelling?

Thanks again,

Bartok

You don't want to store the text description. You DO want to store it exactly as it has. You just have to create queries that include the table which has the description so that you can display the value. This has to do with normalization where you store id numbers for values from lookup tables. This allows a couple of things. It is more efficient for indexing, which helps the program with finding things and speed. It also keeps you from having to go change the text in multiple places if something changes. So, let's say you have a product in a products table called "Big Shoe" and then you store that description in every sale that you make for "Big Shoe." If the manufacturer changes the name to "Bigg Shoe," if you changed the name in the products table, but not in all of the previous sales, you would not be able to create meaningful queries with the products table and previous entered data. But, if the ID of the product "Big Shoe" were to be, say 233, and you stored 233 in every sale, and the manufacturer changed the name to "Bigg Shoe," you would only have to change the name in the products table because in the sales table all previous sales would still have 233 as the stored value and when you pull a query together you can have the current name, but you didn't have to go change any other data.

I hope that makes sense.

Big Shoe or Bigg Shoe
are you trying to tell us something Bob (Your not a circus clown on the side are you ?)

Big Shoe or Bigg Shoe
are you trying to tell us something Bob (Your not a circus clown on the side are you ?)

Ah Gary, you guessed my secret hobby... NOT! :cool:

Thanks Bob,

That makes sense, and I did create a 'Titles' table referenced by the combo box. I just can;t work out how to get the label wizard, working on a query generated by 'filter by form' to return the text value rather than the code.

The users are much happier with 'filter by form, than with creating queries directly; so it's the perfect way for them to make address labels.

Thanks,

Bartok

Hope you don't mind if i bump this. I'm badly stuck.

bartok