IIf question

  • Thread starter Thread starter Mattias
  • Start date Start date
M

Mattias

I have a IIf in a calculated control in a form and it calculates a null
value in some records....I do not want that to happen. Can anyone see
anything wrong here....

Mattias

=IIf([BeloppAttBetala]=0;13;IIf([BeloppAttBetala]>0 And
[BeloppAttBetala]<[Total];12;IIf([StatusKöp2]=10;10;IIf([StatusKöp2]=11;11))
))
 
Looks like a logic error to me.

What happens when BeloppAttBetala is => Total?

or

When BeloppAttBetala > 0 and < Total, but StatusKöp2 <> 10 or 11?

or

when either BeloppAttBetala or StatusKöp2 are Null?

Ron W
 
Hi

Thank you for your reply..I am not very good at these IIf:s
Can you please me to rewrite so it works with your suggestion as well

Mattias
Ron Weiner said:
Looks like a logic error to me.

What happens when BeloppAttBetala is => Total?

or

When BeloppAttBetala > 0 and < Total, but StatusKöp2 <> 10 or 11?

or

when either BeloppAttBetala or StatusKöp2 are Null?

Ron W
Mattias said:
I have a IIf in a calculated control in a form and it calculates a null
value in some records....I do not want that to happen. Can anyone see
anything wrong here....

Mattias

=IIf([BeloppAttBetala]=0;13;IIf([BeloppAttBetala]>0 And
Click to expand...
[BeloppAttBetala]<[Total];12;IIf([StatusKöp2]=10;10;IIf([StatusKöp2]=11;11))
))
Click to expand...
Click to expand...
 
You are not specifiying the final "false" value for the last IIf function.

=IIf([BeloppAttBetala]=0;13;IIf([BeloppAttBetala]>0 And
[BeloppAttBetala]<[Total];12;IIf([StatusKöp2]=10;10;IIf([StatusKöp2]=11;11;"
PutAValueHereForWhenThisIsFalse"))))
 
Hi again

Can you help me with the correct syntax for this function, thanks!

Mattias

Function X() As Integer
If IsNull(BeloppAttBetala) Then X = 13
If BeloppAttBetala = 0 Then X = 13
If BeloppAttBetala > 0 Then If BeloppAttBetala < Total Then X = 12
If Nz(StatusKöp2, 0) = 10 Then X = 10
If Nz(StatusKöp2, 0) = 11 Then X = 11
'if BeloppAttBetala > 0 and < Total StatusKöp2 <> 10 or 11 then X = 12
'End If
If BeloppAttBetala < 0 Then X = 14

End Function
Ron Weiner said:
As a general rule I do not like to use more that 2 or 3 nested IIF's as they
are just too darn hard to debug. If I understand what you want to do here
is some pseudo code to get it done.

function X () as integer
if isnull(BeloppAttBetala ) then
X=whatever you want here when BeloppAttBetala is null
else if BeloppAttBetala = 0 then
X = 13
else if BeloppAttBetala > 0 then
if BeloppAttBetala < Total then
X =12
else if nz(StatusKöp2,0) =10 then
X = 10
else if nz(StatusKöp2,0) = 11 then
X = 11
else
X = something else when BeloppAttBetala is between 0 and less
than Total but StatusKöp2 dosent = 10 or 11
end if
else
X=yet another value when BeloppAttBetala < 0
end if
end function

Call the funcction from the afterupdate event of the BeloppAttBetala and
StatusKöp2 fields and the OnCurrent event of the form
fieldIamUpdating.value = X()

Ron W

Mattias said:
Hi

Thank you for your reply..I am not very good at these IIf:s
Can you please me to rewrite so it works with your suggestion as well

Mattias
Ron Weiner said:
Looks like a logic error to me.

What happens when BeloppAttBetala is => Total?

or

When BeloppAttBetala > 0 and < Total, but StatusKöp2 <> 10 or 11?

or

when either BeloppAttBetala or StatusKöp2 are Null?

Ron W
I have a IIf in a calculated control in a form and it calculates a null
value in some records....I do not want that to happen. Can anyone see
anything wrong here....

Mattias

=IIf([BeloppAttBetala]=0;13;IIf([BeloppAttBetala]>0 And
Click to expand...
Click to expand...
[BeloppAttBetala]<[Total];12;IIf([StatusKöp2]=10;10;IIf([StatusKöp2]=11;11))
Click to expand...
 
Hi Ron

Excactly what I meant...I had commented (')the lines in the function that do
not work ok

Mattias
Ron Weiner said:
I do not see any problem with the syntax of the function as you have it. It
should run without returning any errors. However I am not sure of the
logic. I see some instances where the function will return a 0.

If BeloppAttBetala is > Total and StatusKöp2 is not 11 or 12 for
instance.

Sorry I can't be of more help, but I do not understand what the function is
suppose to do.

Ron W
Mattias said:
Hi again

Can you help me with the correct syntax for this function, thanks!

Mattias

Function X() As Integer
If IsNull(BeloppAttBetala) Then X = 13
If BeloppAttBetala = 0 Then X = 13
If BeloppAttBetala > 0 Then If BeloppAttBetala < Total Then X = 12
If Nz(StatusKöp2, 0) = 10 Then X = 10
If Nz(StatusKöp2, 0) = 11 Then X = 11
'if BeloppAttBetala > 0 and < Total StatusKöp2 <> 10 or 11 then X = 12
'End If
If BeloppAttBetala < 0 Then X = 14

End Function
Ron Weiner said:
As a general rule I do not like to use more that 2 or 3 nested IIF's
Click to expand...
as
they
are just too darn hard to debug. If I understand what you want to do here
is some pseudo code to get it done.

function X () as integer
if isnull(BeloppAttBetala ) then
X=whatever you want here when BeloppAttBetala is null
else if BeloppAttBetala = 0 then
X = 13
else if BeloppAttBetala > 0 then
if BeloppAttBetala < Total then
X =12
else if nz(StatusKöp2,0) =10 then
X = 10
else if nz(StatusKöp2,0) = 11 then
X = 11
else
X = something else when BeloppAttBetala is between 0 and less
than Total but StatusKöp2 dosent = 10 or 11
end if
else
X=yet another value when BeloppAttBetala < 0
end if
end function

Call the funcction from the afterupdate event of the BeloppAttBetala and
StatusKöp2 fields and the OnCurrent event of the form
fieldIamUpdating.value = X()

Ron W

Hi

Thank you for your reply..I am not very good at these IIf:s
Can you please me to rewrite so it works with your suggestion as well

Mattias
Looks like a logic error to me.

What happens when BeloppAttBetala is => Total?

or

When BeloppAttBetala > 0 and < Total, but StatusKöp2 <> 10 or 11?

or

when either BeloppAttBetala or StatusKöp2 are Null?

Ron W
I have a IIf in a calculated control in a form and it calculates a
null
value in some records....I do not want that to happen. Can
Click to expand...
anyone
see
anything wrong here....

Mattias

=IIf([BeloppAttBetala]=0;13;IIf([BeloppAttBetala]>0 And
Click to expand...
Click to expand...
[BeloppAttBetala]<[Total];12;IIf([StatusKöp2]=10;10;IIf([StatusKöp2]=11;11))
Click to expand...
 
Back
Top