Solved Need to test 3 values to assign new value? (1 Viewer)

[kawai6rr]

I have 3 separate field counts and if all 3 counts come up as NULL then it needs to be an X or else nothing.

NoPatOcc: IsNull( [P0_Count] And IsNull( [P1_Count] And IsNull( [P2_Count], "X", "")

wrong number of arguments?

 

[Gasman]

Forgot the IIF ?
Plus you need to end the ISNULL function after each field name?

 

[theDBguy]

Hi. Just a guess but maybe try something like

IIf(Nz(Field1,"x") & Nz(Field2,"x") & Nz(Field3,"x")="xxx","X","SomethingElse")

 

[Pat Hartman]

The code suggested by theDBGuy should solve your immediate problem but storing calculated values is generally poor practice. It would probably be better to calculate this value in a query rather than storing it. If you store it, you have to be exceptionally diligent about ensuring that it is ALWAYS valid.

Also, you have a repeating group so you might consider rethinking your table schema.

 

[kawai6rr]

Thanks for the responses with your input I was able to get it figured out.

NoPatOcc: IIF (IsNull([P0_Count]), IIf(IsNull([P1_Count]), IIf(IsNull([P2_Count]), "X", " ")))