Calculated field in a Query using IIF & OR DLOOKUP

[cornforth]

I have created a query where one of the fields was created using the Lookup Wizard when the main table was constructed. I now wish to create a calculated field using one of these fields. I can get the query to run but the calculated field only shows the "ID" column and not the second column showing the text of the second table.
The result is the same for both Dlookup & IIf

Cand anybody help

 

[plog]

Can you demonstrate your issue with data? Provide 2 sets:

A. Starting data from your table. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me what data you hope to end up with if you feed your query the data in A.

 

[Pat Hartman]

Remove all lookups from tables. Lookups on forms - GOOD. Lookups on tables - BAD.

Lookups on tables hide the actual contents of the field. you usually "see" a text value such as customer name but in reality the field value is numeric and this causes immense confusion once you start using queries and code.

Then, when you want to include the text value - for example to show the customer name on an invoice form, you would Join the customer table to the invoice table on customerID and in the query select the customer demographic details and display them on the form in bound controls. Typically "lookup" data when displayed this way should be locked to prevent accidental changes. You don't want the user accidentally changing the customer name on the invoice form. To lock the fields, set the lock property to yes for each control that should not be updated.

We're probably going to need a picture of the schema and the SQL for the query to go any further.