Report showing ID value rather than value in the query

shart68
shart68 used Ask the Experts™
on
I have a report that works off of a filtered query.  Everything works correctly on the report except a field shows the ID value rather than the value of the record.  The query that brings the data in shows the correct value in the field.  How do I fix?

Example in the report:
doc number       description     rev    type    
123                   test doc           A        1     (type should be Instructions)

thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
This this Field in the Table the query is based on look something like the image below ?

mx
Capture1.gif
In design mode right click on the "Type" text box, select Properties. Under the Data tab, look at the Control Source. Make sure it is the same field that contains the instructions. Take into account any other tables that may be related to your query.

Flyster
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
If MX's theory is correct, then this is the reason why most developers *Do Not* use lookup fields in the table.
See here:
http://access.mvps.org/access/lookupfields.htm
Using them in Forms is fine, though...

The other Experts here can outline the techniques to display the Text value instead.

JeffCoachman
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Yes MX - it does look like the example.  What do I need to do to fix this?  Bear in mind that I do have a form where I need to use that lookup in order to display the proper value in the subform.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"where I need to use that lookup in order"
Actually, the greatly preferred way is simply to create that 'lookup' on the Form using a Combo box.
The fix is to eliminate ALL of those combos at the table level, as you can see the kind of confusion they lead to.

mx

Author

Commented:
OK - I removed all the lookup from the table and set the lookup on the form so that is correct.  But I cannot get it to display the value on the report.  And the underlying query shows the ID rather than the value as well.  how can I change it on the query so it displays the value?
MIS Liason
Most Valuable Expert 2012
Commented:
Bring the Value field into the query (the recordsource for the Report.

Then this field will be available for you to insert into the report in design view.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Has this question been resolved?  Can we close the question ?

thx.mx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial