if statement

[#1]

Hi all,

i am trying to build a field based on other fields' values during the LOAD of a table:

// number sec IV answered

          if ([4_1a_Answered] = 1, 1 +

                         if ([4_1b_Answered] = 1, 1 +

                                        if ([4_1c_Answered] = 1, 1 +

                                                       if ([4_1d_Answered] = 1, 1, 0

                                                       ))))                                                                                                                                                                          as [Number Sec IV answered],

The problem is that it does not work, if one ore more *_*_Answered fields are not 1. (If they are <> 1, i would just like to not count them (they can either be 1 or 0).

Thanks for some advise.

K

[#2]

Anyone ? please..

[#3]

Have you tried seperating them into several if statments?

if ([4_1a_Answered] = 1, 1 )

+

if ([4_1b_Answered] = 1, 1 )

+

if ([4_1c_Answered] = 1, 1)

+

if ([4_1d_Answered] = 1, 1)

                                   as [Number Sec IV answered],                

However this may not work either as I am not sure how it will handle the nulls with adding. Can also try this:

if ([4_1a_Answered] = 1, 1,0 )

+

if ([4_1b_Answered] = 1, 1 ,0)

+

if ([4_1c_Answered] = 1, 1,0)

+

if ([4_1d_Answered] = 1, 1,0)

                                   as [Number Sec IV answered],    

[#4 · Accepted solution]

Thanks.

I tried a

RangeSum([4_1a_Answered], [4_1b_Answered], [4_1c_Answered], [4_1d_Answered]) as [Number Sec IV answered]

that seems to work fine.

[jonathandienst]

Hi

The problem is that 1 + Null() = Null(). You can use the Alt() statement to ensure that your addition works.

1 + Alt(Null(), 0) = 1

So use multiple If statements as marcsliving suggested, but wrap each with Alt(...., 0) so that if it is missing or null, then it will return 0.

Regards

Jonathan

[jonathandienst]

Hi

Yes Rangesum handles nulls as 0 so it will work.

Regards

Jonathan