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

kawai6rr

Registered User.
Local time
Yesterday, 18:56
Joined
Aug 26, 2009
Messages
21
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.

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

wrong number of arguments?
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:56
Joined
Sep 21, 2011
Messages
14,048
Forgot the IIF ?
Plus you need to end the ISNULL function after each field name?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:56
Joined
Oct 29, 2018
Messages
21,358
Hi. Just a guess but maybe try something like

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

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:56
Joined
Feb 19, 2002
Messages
42,981
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

Registered User.
Local time
Yesterday, 18:56
Joined
Aug 26, 2009
Messages
21
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", " ")))
 

Users who are viewing this thread

Top Bottom