Access Reports Looking up field value based on another field value

Question

I have a group in my report that groups by the MachineID field and displays the MachineID in the header by a textbox with this Control Source:=[MachineID]. In another table I have an index of MachineID and MachineName. Is there a way to make the textbox get and display the MachineName from the other table instead of just the MachineID from the current table?

Answer 1

You can use DlookUp:

=DlookUp("MachineName","MachineTable","MachineID=" & MachineID)

Make sure that the textbox does not have the same name as a field (column) in the record source.

Answer 2

Ideally, include the field in the record source. All you have to do is link the table with the Machine Name by the MachineID's.

Since you may be using this datasource elsewhere and don't want to change it for some reason, use Remou's suggestion of DLookup.