Calculated field shows keyID instead of value.

[jonasjacobs87]

Hi all,

I have a table dogs (tblHonden) in which i look up breed in a table (Ras, tblHondenrassen) and owner (tblKlanten).
When I try to calculate a field showing [Naam] & " - " & [KlantID] instead of Max - Jonas Jacobs I get Max - 2. The 2 refers to the [KlandID] but instead of showing the name it shows the actual keyID.

Any clue on how I can fix that? I would like to use the "Max - Jonas Jacobs" field in frmHonden to navigate to records on that form.

 

[Pat Hartman]

Based on your expectation, I believe that you are using a lookup field for KlantID. Please remove the lookup from that field. All it is doing is hiding the true value of the field which is fact numeric. Lookups (combos) SHOULD be used on forms but NOT on tables. Users never see tables or queries in a properly defined application. They only see the forms and reports you create for them so the crutch of the table level lookup is really only for you. Once you start making queries and writing code, you will continually run into issues with the lookups not working as you expect them to so get rid of them now.

To fix your query, open the QBE and add tblKlanten to the grid. If you have created a relationship using the relationship window, Access will automatically draw the correct join line. If you haven't created relationships (why not), then you'll need to draw the join line manually. Change the join type to left by double clicking on the middle of the join line and choosing the option that says to show all tblHonden. Then choose the name field from that table.

 

[arnelgp]

Here you may try