Report: Multiple IIF Statements in a control source

[Guest]

H

I have learned how to use an IIf statement in a control source. I now want to put two or more statements in a single control source to drive different results. As an example: If [field 2] = 0 then [field 3]=1; and if [field 2] <>0 then [field 3]=[field 2]-([field 1]+1); if [field 1]=0 then [field 3]=0

Sorry if this all looks confusing but hopefully you get the message. I want this to be all in [field 3] to determine its value

Thank you, Tom

 

[Guest]

Tom

The equation should look like the followin

iif([field2]=0,[field3]=1,iif([field2]<>0,[field3]=[field2]-([field1]+1),)

Please note that you still have an undefined false value in first IIF. I find that writing your multiple IIF statements would be easier if you wrote them separately and the pasted them in either your false or true operation

Brandon

 

[fredg]

Hi

I have learned how to use an IIf statement in a control source. I
now want to put two or more statements in a single control source
to drive different results. As an example: If [field 2] = 0 then
[field 3]=1; and if [field 2] <>0 then [field 3]=[field 2]-([field
1]+1); if [field 1]=0 then [field 3]=0.

Sorry if this all looks confusing but hopefully you get the
message. I want this to be all in [field 3] to determine its value.


Thank you, Tom

As control source in [Control3]....

=IIf([Field2] = 0,1,IIf([Field1]=0,0,[Field2]-[Field1] +1))

What do you want to do if both [Field2] AND [Field1] are 0?

 

[John Vinson]

Hi

I have learned how to use an IIf statement in a control source. I now want to put two or more statements in a single control source to drive different results. As an example: If [field 2] = 0 then [field 3]=1; and if [field 2] <>0 then [field 3]=[field 2]-([field 1]+1); if [field 1]=0 then [field 3]=0.

Sorry if this all looks confusing but hopefully you get the message. I want this to be all in [field 3] to determine its value.

Thank you, Tom

You can use the Switch() function instead, if you have multilayer
logic: its arguments come in pairs; each pair is evaluated in turn,
left to right, and if the first argument of the pair evaluates to TRUE
then the second argument is returned.

I'm curious about your logic though - what if Field1 and Field2 are
BOTH zero? as stated, you have a contradiction - Field3 should be 1
because Field2 is zero, but it should be 0 because field1 is 0.