Need a count of specific text fields 3

[AZdesertdog]

Using CR10/CE10-MSSQL2k
I'm trying to write a formula that looks at several fields in each row. The fields are text fields, named- Q1,Q2,Q3,Q4. In my formula, I want a "1" if ANY of the 4 fields have a "No" value. Some of them have Nulls, some "Yes"s and a few "N/A"s scattered in. I only care about the "No" values and it doesn't matter if 1 or all of the fields have a "No". The only possiblity is for my desired formula field is a 1 or 0. I've tried several "If/Then" versions but can't seem to get it right. Any help appreciated.


trip# Q1 Q2 Q3 Q4 Desiredformula
1234 No Yes N/A 1
1235 No 1
1236 Yes 0
1237 No 1
1238 Yes No No 1
1239 No 1
1240 N/A 0
1241 No No 1
1242 Yes 0

9 Total Trips 6 Total Errors

 

[kskid]

Try this

if
(
{Q1} = "No" or
{Q2} = "No" or
{Q3} = "No" or
{Q4} = "No"
) then
 1
else 
 0

Cheers,

 

[lbass]

I think you either check "convert nulls to default" in report options or use formulas to convert nulls to "" before proceeding, as in:

//{@Q1}:
if isnull({table.Q1}) then "" else {table.Q1}

Then, assuming you use the formula approach, create a formula like:

if instr({@Q1},"No") > 0 or
instr({@Q2},"No") > 0 or
instr({@Q3},"No") > 0 or
instr({@Q4},"No") > 0 then 1 else 0

-LB

 

[synapsevampire]

Try the following:

if "No" in [{table.q1},{table.q2},{table.q3},{table.q4}] then
1
else
0

-k

 

[AZdesertdog]

LB- your suggestion worked, the others gave inconsistant results for some reason. Can you tell me why it worked? I'm not familiar with "inst". Thanks to you all!

 

[lbass]

Actually either of their approaches are perhaps better than mine, which was overkill, except I think the key issue was dealing with the nulls first. Then the other approaches should have worked, too, although maybe an added trim() around the fields might be necessary to eliminate any spaces that may have been entered during data entry.

-LB

 

[synapsevampire]

The difference is the nulls, I overlooked it (eagle eye LB nailed it):

You might still use an array:

Stringvar Array MyArray := MakeArray ({table.q1},{table.q2},{table.q3},{table.q4});
If "No" in MyArray[1 to 4] then
1
else
0

Arrays shouldn't contain nulls in this instance.

As LB described, the File->Report option would make any of the suggestions work without coding for nulls.

-k