IIf Statement not working in Query

[ccook13]

I think I am missing something obvious and am pulling my hair out over this. I have a query with a field called approval type. The choices in approval type are called from a table. The bound column is the Approval type ID number but what the user sees on the screen is the approval type name. One of the options is "AHO Review". What the user sees in the table is "AHO Review". the ID number for this type of approval is 7.

I am modifying an existing query, adding a new field called AHO that is based on this formula:

AHO: IIf([tbl_GB_Master_ProjectLog]![Approval Type]="AHO Review","Yes","No")

When I run the query all the records fill the field with No, even in cases where the Approval Type = AHO Review.

What am I doing wrong?

Thanks!

 

[Micron]

This reads like you've used a table lookup field. If in the table (not query) you can click in that field and you get a drop down list, that is the basis of your problem. General consensus is, table lookup fields = bad. If that is the case, try
AHO: IIf([tbl_GB_Master_ProjectLog]![Approval Type]= 1,"Yes","No") and also try 2 (no quotes) and see what happens. If you get a proper result out of one of those, try

AHO: IIf([tbl_GB_Master_ProjectLog]![Approval Type].Value ="AHO Review","Yes","No") then perhaps read this:

The Access Web - The Evils of Lookup Fields in Tables

 

[ccook13]

Thanks for the reply.

I tried:

AHO: IIf( [tbl_GB_Master_ProjectLog]![Approval Type] =7,"Yes","No")

Result is all records filled in with #Error for this column.

I then tried

AHO: IIf([tbl_GB_Master_ProjectLog]![Approval Type ID]=7,"Yes","No") - Approval Type ID is the numeric field bound to the table

I am asked to enter a parameter value. If the parameter pop up is left blank than all records = No. If I enter 7 then all records = Yes

This formula

AHO: IIf([tbl_GB_Master_ProjectLog]![Approval Type].[Approval Type ID]=7,"Yes","No")

gives the same result.

I looked at the article you referenced and agree this is the root of the problem. Do you have any suggestions? I can find a work around for now but would like to better understand what I did wrong so I don't set myself up to repeat the error later.

 

[Micron]

My suggestion would be to ditch the lookup field (assuming you have them - you didn't clarify) and use lookup tables instead. However, this means you must do data input with forms, which you might not have yet, and the values are chosen from the lookup table using unbound combo box. That's a whole different subject that you might want to research.

Why did you pick 7; there are at least that many approval types? You could try the .Value suggestion as well. I know it's a requirement for criteria when lookup fields are used but not sure about IIF expressions.

 

[ccook13]

I picked 7 because that is the Approval ID number for that approval type. There are not 7 approval types currently but this is an autonumber field and somehow it was incremented up to 7 when that record was created (not by me).

As you might have guessed from that statement, we are using a lookup field (Approval Type) bound to another table (tbl_ApprovalType) as the source for a pick list when filling the field. I'll look into your suggestion to switch to a unbound combo box, since most of the data entry takes place through a form already.

Thanks for the help.

 

[Micron]

Would be interesting if you tried 1 and/or 2 as suggested, because the numbers I suggested have nothing to do with any other field you can see in any table. If in table design view and that field is selected in the field list, and on the lookup tab you see e.g. "Display Control" then maybe combo beside that; "Row Source Type" then Table/query; then perhaps a sql statement for row source, your field is a lookup field. The numbers 1 and 2 would represent the pk values in a hidden system table that you cannot see. The values you see are really in that table, while your visible table shows AHO Review, the real value stored in it is the pk from the hidden table.