Nested IIf Question (1 Viewer)

[danbl]

I am using the IIf function as follows

Exp1:IIf(field A = "a string value",1,0) this returns the correct value.

However when I try to add a second condition using the IIf function, or use the And Or condition, I do not get the correct value.

I am trying to get a value of 1 in the Exp:Col when field B has a value that is different from field A.

What is the correct syntax? What am I missing??

 

[plog]

Post your exact code that is failing, tell us what you expect to get back and what you are actually getting back.

 

[danbl]

The following yields the correct answer of 1.
Exp:IIf([VTEUsed]="Medication",1,0)

If I add a the second condition
Exp:IIf([VTEUsed]="Medication",1,IIf([VTEUsed2]="Sequential",0,0)

What I should see is when condion1 is true but condition2 is also true the value should be 0.

 

[plog]

The second set of code you provided shouldn't return any value--it should give you an error. The number of left parenthesis (2) doesn't equal the number of right parenthesis (1).

If I understand correctly. I think this might best be written without nested Iif statements:

Exp: IIf([VTUsed]="Medication" AND [VTEUsed]<>"Sequential", 1,0)

You don't have multiple conditions, you only have 1 condition with multiple parts. If I understand correctly the only condtion that evaluates to true--is when VTUsed is Medication and VTEUsed2 is not Sequential. That is what the code above does.

 

[danbl]

Not quite there yet .... If both conditions are met then the value needs to be 0, but when only the first condition is met the value needs to be 1.

 

[plog]

And if neither condition is met? When just the second condition is met?

 

[danbl]

If neither condition is met the value is 0.

The second value does not come into play if there is nothing entered for the first condition to evaluate.

 

[plog]

That's exactly what my code does.


A: [VTUsed]="Medication"
B: [VTEUsed2]="Sequential"

The only permutation that results in 1 is if A is true and B is false. You only have 1 condition to test for.

 

[danbl]

This is true but when A is true I need it to return a value of 1.

Is this possible or have I made it too complicated? Should I have a second Exp that only deals with condition A

 

[plog]

I think you've made it to complicated in your mind. Run some test data through my code and give me data where it is returning the wrong value.

 

[danbl]

Below are the results ......The Bold line should have a value of 1



Exp: [VTE Prophylaxsis Used] [VTE Prophylaxsis Used_2]

0 Medication (Pre&Post include low dose ASA Regime Sequentials - L
0 Medication (Pre&Post include low dose ASA Regime Sequentials - L
0
0 Medication (Pre&Post include low dose ASA Regime

 

[plog]

Those field names aren't what you initially said they were. Also, all 3 of the records with 'Medication' values should show 1 since none of the 2nd values are "Sequential".
Can you post your database?

 

[danbl]

It is large and I will have to try to strip it down to the query only. Not working today so I will post it over the weekend. Thanks for your help

 

[danbl]

I want to thank your assistance. You prompted a thought .... to use the IsNull function and that was the piece that was missing. It now calculates exactly as it should . Again Thanks!!!!