IIf statement? Or Choose?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that has three fields with a 'loss reason' where any of the
three may be populated or all could be blank:

Company Loss1 Loss2 Loss3
A No interest
B
C In contract
D No vehicle
E In contract No interest

Using an IIf statement I am trying to create a new field to show the loss
reason where there is one. I can do "IIf(IsNull([Loss1]),([Loss2]),([Loss1])"
but how do I write an if statement that says "if Loss1 AND Loss2 are null,
take Loss3" (e.g. company C) or that leaves the new field empty if there is
no Loss reason e.g. company B? And can the statement combine loss reasons
e.g. company E?

Thank you.
 
Try to use the NZ function instead of IIF
Instead of IIf(IsNull([Loss1]),([Loss2]),([Loss1])
NZ([Loss1],[Loss2])

And to get Loss3 if both Null
NZ(NZ([Loss1],[Loss2]),[Loss3)
 
Thanks for the reply, but is there a formula to say:

If A is null, then take B, but if A & B are null, take C?

Ofer said:
Try to use the NZ function instead of IIF
Instead of IIf(IsNull([Loss1]),([Loss2]),([Loss1])
NZ([Loss1],[Loss2])

And to get Loss3 if both Null
NZ(NZ([Loss1],[Loss2]),[Loss3)


--
In God We Trust - Everything Else We Test


JohnP said:
I have a table that has three fields with a 'loss reason' where any of the
three may be populated or all could be blank:

Company Loss1 Loss2 Loss3
A No interest
B
C In contract
D No vehicle
E In contract No interest

Using an IIf statement I am trying to create a new field to show the loss
reason where there is one. I can do "IIf(IsNull([Loss1]),([Loss2]),([Loss1])"
but how do I write an if statement that says "if Loss1 AND Loss2 are null,
take Loss3" (e.g. company C) or that leaves the new field empty if there is
no Loss reason e.g. company B? And can the statement combine loss reasons
e.g. company E?

Thank you.
Click to expand...
Click to expand...
 
This formula
NZ(NZ([Loss1],[Loss2]),[Loss3])
Take Loss1 but if Loss1 = null take Loss2 But if Loss2 = Null take Loss3

It's the same as
IIf([Loss1] is null,iif([Loss2] is Null,[Loss3],[Loss2]),[Loss1])
but shorter

Try it, that should answer your question.

--
In God We Trust - Everything Else We Test


JohnP said:
Thanks for the reply, but is there a formula to say:

If A is null, then take B, but if A & B are null, take C?

Ofer said:
Try to use the NZ function instead of IIF
Instead of IIf(IsNull([Loss1]),([Loss2]),([Loss1])
NZ([Loss1],[Loss2])

And to get Loss3 if both Null
NZ(NZ([Loss1],[Loss2]),[Loss3)


--
In God We Trust - Everything Else We Test


JohnP said:
I have a table that has three fields with a 'loss reason' where any of the
three may be populated or all could be blank:

Company Loss1 Loss2 Loss3
A No interest
B
C In contract
D No vehicle
E In contract No interest

Using an IIf statement I am trying to create a new field to show the loss
reason where there is one. I can do "IIf(IsNull([Loss1]),([Loss2]),([Loss1])"
but how do I write an if statement that says "if Loss1 AND Loss2 are null,
take Loss3" (e.g. company C) or that leaves the new field empty if there is
no Loss reason e.g. company B? And can the statement combine loss reasons
e.g. company E?

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Thank you, that works perfectly!

Ofer said:
This formula
NZ(NZ([Loss1],[Loss2]),[Loss3])
Take Loss1 but if Loss1 = null take Loss2 But if Loss2 = Null take Loss3

It's the same as
IIf([Loss1] is null,iif([Loss2] is Null,[Loss3],[Loss2]),[Loss1])
but shorter

Try it, that should answer your question.

--
In God We Trust - Everything Else We Test


JohnP said:
Thanks for the reply, but is there a formula to say:

If A is null, then take B, but if A & B are null, take C?

Ofer said:
Try to use the NZ function instead of IIF
Instead of IIf(IsNull([Loss1]),([Loss2]),([Loss1])
NZ([Loss1],[Loss2])

And to get Loss3 if both Null
NZ(NZ([Loss1],[Loss2]),[Loss3)


--
In God We Trust - Everything Else We Test


:

I have a table that has three fields with a 'loss reason' where any of the
three may be populated or all could be blank:

Company Loss1 Loss2 Loss3
A No interest
B
C In contract
D No vehicle
E In contract No interest

Using an IIf statement I am trying to create a new field to show the loss
reason where there is one. I can do "IIf(IsNull([Loss1]),([Loss2]),([Loss1])"
but how do I write an if statement that says "if Loss1 AND Loss2 are null,
take Loss3" (e.g. company C) or that leaves the new field empty if there is
no Loss reason e.g. company B? And can the statement combine loss reasons
e.g. company E?

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Hi John,

I might not understand fully,
but it looks to me that you want
to use a simple concatenation:

([Loss1] + ", ") & ([Loss2] + ", ") & [Loss3]

Of course, I could be wrong...

good luck,

gary

JohnP said:
Thanks for the reply, but is there a formula to say:

If A is null, then take B, but if A & B are null, take C?

Ofer said:
Try to use the NZ function instead of IIF
Instead of IIf(IsNull([Loss1]),([Loss2]),([Loss1])
NZ([Loss1],[Loss2])

And to get Loss3 if both Null
NZ(NZ([Loss1],[Loss2]),[Loss3)


--
In God We Trust - Everything Else We Test


JohnP said:
I have a table that has three fields with a 'loss reason' where any of
the
three may be populated or all could be blank:

Company Loss1 Loss2 Loss3
A No interest
B
C In
contract
D No vehicle
E In contract No
interest

Using an IIf statement I am trying to create a new field to show the
loss
reason where there is one. I can do
"IIf(IsNull([Loss1]),([Loss2]),([Loss1])"
but how do I write an if statement that says "if Loss1 AND Loss2 are
null,
take Loss3" (e.g. company C) or that leaves the new field empty if
there is
no Loss reason e.g. company B? And can the statement combine loss
reasons
e.g. company E?

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top