IIf on Query when linked to multiple tables

[Ekhart]

I have an Iif in a column on the query:

B1: IIf(([Disposition]=4),"Remove from Pending/")

The Disposition field on my main table (tblConsultData) pulls it's selectable values from another table (tblConsultDispositions). When I try to do this statement it returns an #ERROR in the field. I can make this work with fields that don't have a Combo Box pulling values from another table, but in the query it loads the values of the Disposition field as a number value to reflect this (4, for example instead of 'Scheduled' which is what the 4 value represents). Any idea how to make this work?

[aytee111]

Are you saying that you have lookup fields on the table? This is a very bad idea - as you are finding out. Remove them and use queries for all your lookups.

[June7]

The expression should still work. It does for me.

However, I NEVER set lookups in table.

[Ekhart]

No the field is directly in the table, but when users are using this it limits the selection in that field based on options listed in a second table.

[June7]

We understand Disposition is field in table and that field is set with Lookup. I NEVER do this in tables. However, the query expression should still work. I tested.

Users should not interact directly with tables and queries, only forms and reports. Therefore it is of little value to set lookups in table. As developer, when I view tables, I prefer to see actual value saved in field, not the alias.

[ranman256]

Dont use IIF,s. Use a lookup table with all values and their meaings,

code, descr
3, other
4, Disposition

[June7]

@ranman256, Disposition is a field name, not a data value.