Lookup field appears as numerical values instead of text on Access report

ms-access-2010
Marchese Il Chihuahua picture Marchese Il Chihuahua · Jan 21, 2014 · Viewed 66k times · Source

I am trying to create a report putting a field called contact which has the name of a person. This name is linked directly to another table where I keep all the contacts.

For some strange reason, when I include this name (which in query view displays as the name of the contact), instead of the name appearing, the unique ID number is shown on my report.

Answer

Gord Thompson picture Gord Thompson · Jan 22, 2014

As mentioned in the article cited in the above comment, you can use a Combo Box control on your report to do the lookup for you. To see how this can be done, create a new report based on the table containing the lookup field, then drag and drop that field onto the report. That will create a Combo Box control with properties that look something like this:

Row Source: SELECT [Clients].[ID], [Clients].[LastName] FROM Clients;
Bound Column: 1
Column Count: 2
Column Widths: 0";1"

You could use a similar Combo Box control on your actual report to display the client's name rather than their numeric ID value.

Another alternative would be to change the Control Source of the report's Text Box control to have it do a DLookUp() on the table. If the lookup field is named [client] then changing the Control Source of the Text Box to something like

=DLookUp("LastName","Clients","ID=" & [client])

would also work.

Related questions

How to insert values into the database table using VBA in MS access

I've started to use access recently. I am trying to insert a few rows into the database; however, I am stuck as it is throwing an error: Too few parameters. I have a table test with only one column in …

Read More
Corrupted Access .accdb file: "Unrecognized Database Format"

I'm having a problem. One of my databases on our shared network will not open for any user. It says "Unrecognized Database Format." I've had this problem before but was still able to open the database, compact and repair, or …

Read More
Access 2010 VBA query a table and iterate through results

I have a query that I want to execute against a table. With the results I want to do something. In my head the pseudo code is: var q = "select * from table where some condition"; var results = db.getResults(q); …

Read More