Getting a report to show a looked up value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

canusa - 22 Dec 2004 10:29 GMT

I had a report in access 97 that worked fine. When converted to access 2000
it does not report the same. The report is based on a query. In the query,
one of the columns refers to a table and the field in that table is a lookup
value. The lookup field looks for a customer's number and then goes to the
next column to report the customer name. When the query shows the data, it
does not show the customer number - but it does show the customer name - and
that is just what I want. However, when the report comes out, it does not
show the customer name - it shows the customer number. Any help would be
appreciated.

Signature

canusa@discussions.microsoft.com

Reply to this Message

Allen Browne - 22 Dec 2004 16:46 GMT

Open the query for your report in design view.

Add the Customer table to the query. You should see a line joining the
Customer table to your other table, based on the Customer's Number.

Now you can drag the customer name field into the output grid, and then
display it on the report.

If you have some records in your other table where the customer number is
null (blank), you will need to use an outer join or you won't get all your
records. More info:
http://members.iinet.net.au/~allenbrowne/casu-02.html

The lookup field is notorious for confusing people, so many of us hate it:
http://www.mvps.org/access/lookupfields.htm

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I had a report in access 97 that worked fine. When converted to access 2000
> it does not report the same. The report is based on a query. In the query,
[quoted text clipped - 7 lines]
> show the customer name - it shows the customer number. Any help would be
> appreciated.

Reply to this Message