Report displays numbers, not appropriate categories

[Denniso6]

I’m populating a combo-box (that appears in my main table and a few other
forms) using the contents of another small table. There are only two fields
in this small table; ‘ID’ (AutoNumber) and ‘Categories’ (Text).

The associated combo-box field (named ‘Category’) that appears within the
main table (used to record support incidents) has a data type of number.

All tables and forms work as expected up to this point.

The Problem: In a report, I’m attempting to group incidents by their
assigned category. Unfortunately, the categories display on the report as
numbers; 1, 2, 3 and so on when they should display as ‘Hardware’,
‘Software’, ‘Services’ and so on’

I’d appreciate any ideas as to what I may be doing wrong.

DennisO

 

[Wayne-I-M]

Your combo box in the report is displaying the bound column (the ID field).

Open the report in design view and set the bound column to 0

0cm;2cm

 

[Ken Sheridan]

What you are seeing are the true values of the foreign key column in your
main (referencing) table. What you are seeing in your main table in
datasheet view are the values looked up from the (referenced) categories
table. You've either used the 'look up wizard' when designing the table or
have manually set the display control to a combo box.

To show the category values in your report join the main table to the
categories table on the ID columns in a query, and make the query the
RecordSource property of the report. Return all the columns from your main
table, apart from the foreign key column, and include the categories column
from the categories table in the query. Then change the ControlSource
property of the relevant text box in the report to the categories column.
You'll also need to change the GroupLevel to that column in the report's
Sorting and Grouping dialogue.

BTW its generally considered by experienced Access developers that the look
up wizard is best avoided. It serves no real purpose and you can use a combo
box in a form or join tables in a query to see the referenced value. Seeing
the true value in raw datasheet view of a table is no loss as data should
only be viewed and/or edited via forms and reports, not via a raw datasheet.
See the following link for the gory details:


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



Ken Sheridan
Stafford, England

 

[Ken Sheridan]

I doubt it, Wayne. Its much more likely that a text box control is bound to
the Category column from the referencing table and is showing the true value
of the foreign key.

Using a combo box in a report is unusual, but there is one circumstance
where it is necessary; where the Filter property of a form which has been
filtered 'by form' or 'by selection' is passed to report to restrict its
results to that of the filtered form. Because of the way the Filter property
is built when a combo box is used in a form to display values from a
referenced table, the Filter property would not be applicable to a report
which joins the referencing and referenced tables as is usual for a report's
RecordSource.

Ken Sheridan
Stafford, England

 

[Wayne-I-M]

If he has a combo in the report design the "normal" (?) error would be to
simply show the bound column as that is what would normally be stored (and so
reported - if he has used the wizard to create the control).

You may be right - but better to try the most obvious reason before looking
for other solutions

 

[Ken Sheridan]

I think mine is the most obvious reason. I'll put a fiver on it!

Ken Sheridan
Stafford, England

 

[Wayne-I-M]

I've never seen that much money in one place

 

[Ken Sheridan]

Well I'll have to put the missus out on the streets if you win!

Ken Sheridan
Stafford, England

 

[Denniso6]

Thanks so much Wayne but this didn't seem to work.

The result was that numbers still appear in place of the Categores but the
sequence seems to be off-set upward by 2; that is instead of the sequence
1,2,3..., what is displayed is 3,4,5...

I'll keep working on it.

Thanks again for your assistance.

DennisO
---------------

 

[Wayne-I-M]

Can you post the source of the combo you are using in your report

Open the report in design view and right click the combo in the data column
there should be a source. Can you cut and paste it here so we can see.

If the source is a query can you post the sql of the query

 

[Ken Sheridan]

Don't use a combo box in the report, join the tables in the report's
underlying query and bind a combo box to the Categories text column from the
referenced table. There is no point in using a combo box solely for display
purposes, but only where the user needs the ability to select values form a
list to assign a value, i.e. in a form. In a report a combo box merely
serves to inhibit performance. I know the Access wizardry does use a combo
box if the underlying table has been set up with the evil look up field
facility, but that's only compounding the acknowledged stupidity of this
feature. Do it the right way not the Microserfs' way!

Grouping the report on the text column will also order the groups
alphabetically rather than by the arbitrary numeric values of an autonumber
column, which is usually what's wanted, but if you want to impose an order
independent of the text values then a separate CategoryOrder column can be
included in the categories table with values 100, 200, 300 etc in the order
you want the categories listed. Numbering like this allows for any new
categories which might be added to the table to be given a number within the
sequence without having to update the values of the rest, which would be the
case if values 1,2,3 etc were used. You can then group the report on
CategoryOrder.

Ken Sheridan
Stafford, England