IIf then

  • Thread starter Thread starter JohnLute
  • Start date Start date
J

JohnLute

I have an unbound text box in a report where I want to do a simple calculation.

IIf [UnitCount] has data then [UnitSize]/[UnitCount]
IIf [SubUnitCount] has data then [UnitSize]/[UnitCount]

I can't seem to get the syntax right. Any help would be appreciated.

THANKS!
 
Try something like this:
=IIf([UnitCount] <> 0, [UnitSize] / [UnitCount], Null)

That avoids the division by zero error.
 
Thanks, Allen.

I just realized how badly I blew this post! I didn't at all accurately
explain myself. Let me try this way.

I have three fields:
UnitCount
SubUnitCount
UnitSize

There are always UnitCount and UnitSize values however there may or may not
be a value for SubUnitCount. For example:

UnitCount | SubUnitCount | UnitSize
12 |6 |3
12 | |2

I wrote this up as:
IIf [UnitCount] has data then [UnitSize]/[UnitCount]
IIf [SubUnitCount] has data then [UnitSize]/[UnitCount]

What this doesn't account for is
IIf [SubUnitCount] does NOT have data then [UnitSize]/[UnitCount]

Sorry about that!
--
www.Marzetti.com


Allen Browne said:
Try something like this:
=IIf([UnitCount] <> 0, [UnitSize] / [UnitCount], Null)

That avoids the division by zero error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

JohnLute said:
I have an unbound text box in a report where I want to do a simple
calculation.

IIf [UnitCount] has data then [UnitSize]/[UnitCount]
IIf [SubUnitCount] has data then [UnitSize]/[UnitCount]

I can't seem to get the syntax right. Any help would be appreciated.

THANKS!
Click to expand...
Click to expand...
 
You have described UnitSize/UnitCount in all cases. I don't think that's
what you meant. If it is what you meant, the problem is unclear.

JohnLute said:
Thanks, Allen.

I just realized how badly I blew this post! I didn't at all accurately
explain myself. Let me try this way.

I have three fields:
UnitCount
SubUnitCount
UnitSize

There are always UnitCount and UnitSize values however there may or may
not
be a value for SubUnitCount. For example:

UnitCount | SubUnitCount | UnitSize
12 |6 |3
12 | |2

I wrote this up as:
IIf [UnitCount] has data then [UnitSize]/[UnitCount]
IIf [SubUnitCount] has data then [UnitSize]/[UnitCount]

What this doesn't account for is
IIf [SubUnitCount] does NOT have data then [UnitSize]/[UnitCount]

Sorry about that!
--
www.Marzetti.com


Allen Browne said:
Try something like this:
=IIf([UnitCount] <> 0, [UnitSize] / [UnitCount], Null)

That avoids the division by zero error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

JohnLute said:
I have an unbound text box in a report where I want to do a simple
calculation.

IIf [UnitCount] has data then [UnitSize]/[UnitCount]
IIf [SubUnitCount] has data then [UnitSize]/[UnitCount]

I can't seem to get the syntax right. Any help would be appreciated.

THANKS!
Click to expand...
Click to expand...
Click to expand...
 
Hi, Bruce.

Sorry - I'm not being very clear. There are always UnitCount and UnitSize
values however when there is a SubUnitCount then UnitSize must be divided by
it regardless of the value in UnitCount.

For example:
UnitCount | SubUnitCount | UnitSize
12 |6 |3 = 2 (3/6)
12 | |2 = 6 (2/12)

I hope that makes more sense!

--
www.Marzetti.com


BruceM said:
You have described UnitSize/UnitCount in all cases. I don't think that's
what you meant. If it is what you meant, the problem is unclear.

JohnLute said:
Thanks, Allen.

I just realized how badly I blew this post! I didn't at all accurately
explain myself. Let me try this way.

I have three fields:
UnitCount
SubUnitCount
UnitSize

There are always UnitCount and UnitSize values however there may or may
not
be a value for SubUnitCount. For example:

UnitCount | SubUnitCount | UnitSize
12 |6 |3
12 | |2

I wrote this up as:
IIf [UnitCount] has data then [UnitSize]/[UnitCount]
IIf [SubUnitCount] has data then [UnitSize]/[UnitCount]

What this doesn't account for is
IIf [SubUnitCount] does NOT have data then [UnitSize]/[UnitCount]

Sorry about that!
--
www.Marzetti.com


Allen Browne said:
Try something like this:
=IIf([UnitCount] <> 0, [UnitSize] / [UnitCount], Null)

That avoids the division by zero error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an unbound text box in a report where I want to do a simple
calculation.

IIf [UnitCount] has data then [UnitSize]/[UnitCount]
IIf [SubUnitCount] has data then [UnitSize]/[UnitCount]

I can't seem to get the syntax right. Any help would be appreciated.

THANKS!
Click to expand...
Click to expand...
Click to expand...
 
At the risk of seeming overly picky, either UnitCount or SubUnitCount is
divided by UnitSize to get the desired answers, not the other way around.
That being said, maybe something like this:
=IIf(Nz([SubUnitCount],0) <>
0,[SubUnitCount]/[UnitSize],[UnitCount]/[UnitSize])
The Nz function guards against a null value in SubUnitCount, which could
cause an error. Help has more information about Nz.

JohnLute said:
Hi, Bruce.

Sorry - I'm not being very clear. There are always UnitCount and UnitSize
values however when there is a SubUnitCount then UnitSize must be divided
by
it regardless of the value in UnitCount.

For example:
UnitCount | SubUnitCount | UnitSize
12 |6 |3 = 2 (3/6)
12 | |2 = 6 (2/12)

I hope that makes more sense!

--
www.Marzetti.com


BruceM said:
You have described UnitSize/UnitCount in all cases. I don't think that's
what you meant. If it is what you meant, the problem is unclear.

JohnLute said:
Thanks, Allen.

I just realized how badly I blew this post! I didn't at all accurately
explain myself. Let me try this way.

I have three fields:
UnitCount
SubUnitCount
UnitSize

There are always UnitCount and UnitSize values however there may or may
not
be a value for SubUnitCount. For example:

UnitCount | SubUnitCount | UnitSize
12 |6 |3
12 | |2

I wrote this up as:
IIf [UnitCount] has data then [UnitSize]/[UnitCount]
IIf [SubUnitCount] has data then [UnitSize]/[UnitCount]

What this doesn't account for is
IIf [SubUnitCount] does NOT have data then [UnitSize]/[UnitCount]

Sorry about that!
--
www.Marzetti.com


:

Try something like this:
=IIf([UnitCount] <> 0, [UnitSize] / [UnitCount], Null)

That avoids the division by zero error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an unbound text box in a report where I want to do a simple
calculation.

IIf [UnitCount] has data then [UnitSize]/[UnitCount]
IIf [SubUnitCount] has data then [UnitSize]/[UnitCount]

I can't seem to get the syntax right. Any help would be appreciated.

THANKS!
Click to expand...
Click to expand...
Click to expand...
 
Thanks for being overly picky, Bruce!
--
www.Marzetti.com


BruceM said:
At the risk of seeming overly picky, either UnitCount or SubUnitCount is
divided by UnitSize to get the desired answers, not the other way around.
That being said, maybe something like this:
=IIf(Nz([SubUnitCount],0) <>
0,[SubUnitCount]/[UnitSize],[UnitCount]/[UnitSize])
The Nz function guards against a null value in SubUnitCount, which could
cause an error. Help has more information about Nz.

JohnLute said:
Hi, Bruce.

Sorry - I'm not being very clear. There are always UnitCount and UnitSize
values however when there is a SubUnitCount then UnitSize must be divided
by
it regardless of the value in UnitCount.

For example:
UnitCount | SubUnitCount | UnitSize
12 |6 |3 = 2 (3/6)
12 | |2 = 6 (2/12)

I hope that makes more sense!

--
www.Marzetti.com


BruceM said:
You have described UnitSize/UnitCount in all cases. I don't think that's
what you meant. If it is what you meant, the problem is unclear.

Thanks, Allen.

I just realized how badly I blew this post! I didn't at all accurately
explain myself. Let me try this way.

I have three fields:
UnitCount
SubUnitCount
UnitSize

There are always UnitCount and UnitSize values however there may or may
not
be a value for SubUnitCount. For example:

UnitCount | SubUnitCount | UnitSize
12 |6 |3
12 | |2

I wrote this up as:
IIf [UnitCount] has data then [UnitSize]/[UnitCount]
IIf [SubUnitCount] has data then [UnitSize]/[UnitCount]

What this doesn't account for is
IIf [SubUnitCount] does NOT have data then [UnitSize]/[UnitCount]

Sorry about that!
--
www.Marzetti.com


:

Try something like this:
=IIf([UnitCount] <> 0, [UnitSize] / [UnitCount], Null)

That avoids the division by zero error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an unbound text box in a report where I want to do a simple
calculation.

IIf [UnitCount] has data then [UnitSize]/[UnitCount]
IIf [SubUnitCount] has data then [UnitSize]/[UnitCount]

I can't seem to get the syntax right. Any help would be appreciated.

THANKS!
Click to expand...
Click to expand...
Click to expand...
 
Any time!

JohnLute said:
Thanks for being overly picky, Bruce!
--
www.Marzetti.com


BruceM said:
At the risk of seeming overly picky, either UnitCount or SubUnitCount is
divided by UnitSize to get the desired answers, not the other way around.
That being said, maybe something like this:
=IIf(Nz([SubUnitCount],0) <>
0,[SubUnitCount]/[UnitSize],[UnitCount]/[UnitSize])
The Nz function guards against a null value in SubUnitCount, which could
cause an error. Help has more information about Nz.

JohnLute said:
Hi, Bruce.

Sorry - I'm not being very clear. There are always UnitCount and
UnitSize
values however when there is a SubUnitCount then UnitSize must be
divided
by
it regardless of the value in UnitCount.

For example:
UnitCount | SubUnitCount | UnitSize
12 |6 |3 = 2 (3/6)
12 | |2 = 6 (2/12)

I hope that makes more sense!

--
www.Marzetti.com


:

You have described UnitSize/UnitCount in all cases. I don't think
that's
what you meant. If it is what you meant, the problem is unclear.

Thanks, Allen.

I just realized how badly I blew this post! I didn't at all
accurately
explain myself. Let me try this way.

I have three fields:
UnitCount
SubUnitCount
UnitSize

There are always UnitCount and UnitSize values however there may or
may
not
be a value for SubUnitCount. For example:

UnitCount | SubUnitCount | UnitSize
12 |6 |3
12 | |2

I wrote this up as:
IIf [UnitCount] has data then [UnitSize]/[UnitCount]
IIf [SubUnitCount] has data then [UnitSize]/[UnitCount]

What this doesn't account for is
IIf [SubUnitCount] does NOT have data then [UnitSize]/[UnitCount]

Sorry about that!
--
www.Marzetti.com


:

Try something like this:
=IIf([UnitCount] <> 0, [UnitSize] / [UnitCount], Null)

That avoids the division by zero error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an unbound text box in a report where I want to do a simple
calculation.

IIf [UnitCount] has data then [UnitSize]/[UnitCount]
IIf [SubUnitCount] has data then [UnitSize]/[UnitCount]

I can't seem to get the syntax right. Any help would be
appreciated.

THANKS!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top