Nested IIF Query Issue

[wbarker]

I have a query linked to the following table which I am trying to extract data from.

t_FDA_Product_Class_Code_Compliance_Date


ClassCode Description
0 0
1 1
2 2
3 3
9 9
Blank 0
F F
FALSE 0
N N
U U


The expression we are trying to use is as follows:

Expr1: IIf([t_FDA_Product_Code_List]![Product Code] Is Null,0,iif([t_FDA_Product_Class_Code_Compliance_Date]![Description] IN (F,N,U), then ([t_FDA_Product_Class_Code_Compliance_Date]![Description] ELSE
Max(t_FDA_Product_Class_Code_Compliance_Date]![Description])))


Where if the value from the table is blank.. then zero
if IN (F,N,U) then (F,N,U)
If it is a number, then return the max value of that number.


I get "the expression you entered contains invalid syntax, you may have entered an operand without an operator"

 

[jorismoerings]

You missed some closing brackets.
Try this:

Expr1: IIf([t_FDA_Product_Code_List]![Product Code] Is Null,0,iif([t_FDA_Product_Class_Code_Compliance_Date]![Description] IN (F,N,U), ([t_FDA_Product_Class_Code_Compliance_Date]![Description]), Max(t_FDA_Product_Class_Code_Compliance_Date]![Description])))

 

[wbarker]

You missed some closing brackets.
Try this:

Expr1: IIf([t_FDA_Product_Code_List]![Product Code] Is Null,0,iif([t_FDA_Product_Class_Code_Compliance_Date]![Description] IN (F,N,U), ([t_FDA_Product_Class_Code_Compliance_Date]![Description]), Max(t_FDA_Product_Class_Code_Compliance_Date]![Description])))

Received "The Expression you entered contains invalid syntax...You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding quotation marks.

I added quotation marks on "F", "N", "U" to no avail...

 

[jorismoerings]

ok, did you check if Max([t_FDA_Product_Class_Code_Compliance_Date]![Description]) is a numeric field. It seems like the name "Description" isn't a numeric field but looks like a Text field.

 

[james_lankford]

so you're doing this

IIf
(
  [t_FDA_Product_Code_List]![Product Code] Is Null, 
  0, 
  iif
  (
    [t_FDA_Product_Class_Code_Compliance_Date]![Description] IN ('F', 'N', 'U'), 
    [t_FDA_Product_Class_Code_Compliance_Date]![Description], 
    Max(t_FDA_Product_Class_Code_Compliance_Date]![Description])
  )
)

but I don't get the max part

you want the max description for the entire table ?
or the max description for a group of Product Codes or Class Codes ?

its all very confusing (your example table has Class Code, but your sql has Product Code)
and the max description in your table is 9 -- so are you saying you expect to see a 9 ?

can a class code have more than one description ?




the example you showed

 

[xenou]

You'll have a hard time getting max to work here. It does work with text, but '10' will be less than '9' (and you would need a subquery, not a simple function call).