Lookup field displaying id field when I concatenate field in query

Question

My table Events has the following fields:   [Event ID], [Event Title], [Event Date]

Event Title is a lookup of the table Event List with the following fields in this table:  [List ID], [Event] 

When I create a query an attempt to concatenate the [Event Title] and [Event Date] fields from the Events table, the resultant set  shows the  [Event ID] and not the [Event Title]  For example:  5 11/30/2020, instead of Received 11/30/2020

How can i get the actual [Event Title] to display?

Answer 1

Add the Event List table to the query, and join it to the Events table on Event Title vs List ID.

Double-click the join line and select the option to return all records from the Events table.

Use the Event field from the Event List table in the concatenation instead of the Event Title field.

---

Regards, Hans Vogelaar (https://www.eileenslounge.com)

Answer 2

to add to HV's post: it is the List ID that is actually stored in your field...even though when looking at the field (look-up type) it is displaying the Event...

this is Microsoft trying to be helpful but sometimes being confusing instead.... a lot of developers will immediately recommend never using the lookup field type in a table as you can instead implement a lookup at the control (combobox).

Even if you did not include the List ID when initially creating the lookup field - Microsoft added it in automatically.  The field type is probably a number.  

You can go back and manually delete out the List ID and force the lookup to be the Event text....depending on where you are in creation of your db however that can affect downstream stuff that is working ok with the List ID...