Text Boxes - Hopefully a simple IIf Statement !!

  • Thread starter Thread starter Jacob Frankham
  • Start date Start date
J

Jacob Frankham

Hi everyone

I have a grouped textbox [totalRefunds] which is summing the values of other
textboxes, ie

=Sum([refunds])

Now, sometimes refunds are N/A, so what I need to do is this:

If ALL refunds state "N/A" then I want my [totalRefunds] to state "N/A"

Currently, if SOME refunds state "N/A" my [totalRefunds] textbox will ignore
them and Sum the rest.

I am just having trouble displaying "N/A" in the [totalRefunds] textbox for
when ALL [refunds] are "N/A", as it is currently producing "£0.00"

Hope someone can help

Thanks

Jake
 
If we are working with straight numbers for [refunds] then try this;
=Iif(Sum([refunds])=0, "N/A", Sum([refunds]))

Hopefully you are not using the value "N/A" as a valid entry in this field!

HTH,
Mich
 
Hi

That isn't possible, because sometimes [refunds] will all be £0.00 making a
sum of £0.00

It is just if ALL [refunds] are "N/A" then I want to write "N/A" in the
[totalRefund] field

Help !!!

Cheers

Jake

M Skabialka said:
If we are working with straight numbers for [refunds] then try this;
=Iif(Sum([refunds])=0, "N/A", Sum([refunds]))

Hopefully you are not using the value "N/A" as a valid entry in this field!

HTH,
Mich

Jacob Frankham said:
Hi everyone

I have a grouped textbox [totalRefunds] which is summing the values of other
textboxes, ie

=Sum([refunds])

Now, sometimes refunds are N/A, so what I need to do is this:

If ALL refunds state "N/A" then I want my [totalRefunds] to state "N/A"

Currently, if SOME refunds state "N/A" my [totalRefunds] textbox will ignore
them and Sum the rest.

I am just having trouble displaying "N/A" in the [totalRefunds] textbox for
when ALL [refunds] are "N/A", as it is currently producing "£0.00"

Hope someone can help

Thanks

Jake
Click to expand...
Click to expand...
 
How about a hidden field? Say [NAField] on the same detail line as [refunds]

[NAField]
=Iif([refunds]="N/A", 0,1)

[totalRefunds]
=Iif(Sum([NAField])=0, "N/A", Sum([refunds]))

Mich

Jacob Frankham said:
Hi

That isn't possible, because sometimes [refunds] will all be £0.00 making a
sum of £0.00

It is just if ALL [refunds] are "N/A" then I want to write "N/A" in the
[totalRefund] field

Help !!!

Cheers

Jake

M Skabialka said:
If we are working with straight numbers for [refunds] then try this;
=Iif(Sum([refunds])=0, "N/A", Sum([refunds]))

Hopefully you are not using the value "N/A" as a valid entry in this field!

HTH,
Mich

Jacob Frankham said:
Hi everyone

I have a grouped textbox [totalRefunds] which is summing the values of other
textboxes, ie

=Sum([refunds])

Now, sometimes refunds are N/A, so what I need to do is this:

If ALL refunds state "N/A" then I want my [totalRefunds] to state "N/A"

Currently, if SOME refunds state "N/A" my [totalRefunds] textbox will ignore
them and Sum the rest.

I am just having trouble displaying "N/A" in the [totalRefunds]
Click to expand...
textbox
for
when ALL [refunds] are "N/A", as it is currently producing "£0.00"

Hope someone can help

Thanks

Jake
Click to expand...
Click to expand...
Click to expand...
 
Jacob said:
I have a grouped textbox [totalRefunds] which is summing the values of other
textboxes, ie

=Sum([refunds])

Now, sometimes refunds are N/A, so what I need to do is this:

If ALL refunds state "N/A" then I want my [totalRefunds] to state "N/A"

Currently, if SOME refunds state "N/A" my [totalRefunds] textbox will ignore
them and Sum the rest.

I am just having trouble displaying "N/A" in the [totalRefunds] textbox for
when ALL [refunds] are "N/A", as it is currently producing "£0.00"
Click to expand...

What is in the refunds field (not the text box that's
displaying the field) that indicates it is "N/A". Since
you're summing the field, it must be a numeric (Currency?)
type of field, which can only contain a number or Null.

If you're interpreting a Null in the field to mean "N/A",
then you could use the expression:

=IIf(Count(refunds) = 0, "N/A", Sum(refunds))
 
Hi

No, [refunds] is Currency
But if it is Null I am changing it to "N/A"

Thus in [totalRefunds] which is also currency, I wish to see "N/A" only if
ALL [refunds] are "N/A"

Cheers

Jake
Marshall Barton said:
Jacob said:
I have a grouped textbox [totalRefunds] which is summing the values of other
textboxes, ie

=Sum([refunds])

Now, sometimes refunds are N/A, so what I need to do is this:

If ALL refunds state "N/A" then I want my [totalRefunds] to state "N/A"

Currently, if SOME refunds state "N/A" my [totalRefunds] textbox will ignore
them and Sum the rest.

I am just having trouble displaying "N/A" in the [totalRefunds] textbox for
when ALL [refunds] are "N/A", as it is currently producing "£0.00"
Click to expand...

What is in the refunds field (not the text box that's
displaying the field) that indicates it is "N/A". Since
you're summing the field, it must be a numeric (Currency?)
type of field, which can only contain a number or Null.

If you're interpreting a Null in the field to mean "N/A",
then you could use the expression:

=IIf(Count(refunds) = 0, "N/A", Sum(refunds))
Click to expand...
 
You didn't say it was null!!!

You could still use the hidden field:
eg [NAField] on the same detail line as [refunds]

[NAField]
=Iif(IsNull([refunds]), 0,1)
or
=Iif([refunds] = null, 0,1)

[totalRefunds]
=Iif(Sum([NAField])=0, "N/A", Sum([refunds]))

Mich


Jacob Frankham said:
Hi

No, [refunds] is Currency
But if it is Null I am changing it to "N/A"

Thus in [totalRefunds] which is also currency, I wish to see "N/A" only if
ALL [refunds] are "N/A"

Cheers

Jake
Marshall Barton said:
Jacob said:
I have a grouped textbox [totalRefunds] which is summing the values of other
textboxes, ie

=Sum([refunds])

Now, sometimes refunds are N/A, so what I need to do is this:

If ALL refunds state "N/A" then I want my [totalRefunds] to state "N/A"

Currently, if SOME refunds state "N/A" my [totalRefunds] textbox will ignore
them and Sum the rest.

I am just having trouble displaying "N/A" in the [totalRefunds] textbox for
when ALL [refunds] are "N/A", as it is currently producing "£0.00"
Click to expand...

What is in the refunds field (not the text box that's
displaying the field) that indicates it is "N/A". Since
you're summing the field, it must be a numeric (Currency?)
type of field, which can only contain a number or Null.

If you're interpreting a Null in the field to mean "N/A",
then you could use the expression:

=IIf(Count(refunds) = 0, "N/A", Sum(refunds))
Click to expand...
Click to expand...
 
Jacob said:
No, [refunds] is Currency
But if it is Null I am changing it to "N/A"
Click to expand...

My point was that you can not "change" the content of a
numeric field (including Currency) to contain a text string.
Because of that, I am led to believe that the refunds text
box control in the report is only displaying "N/A" when the
field is Null. (Either that or you're using a query to
calculate the field into a text string that can contain
"N/A", which is not a good way to do this).

Thus in [totalRefunds] which is also currency, I wish to see "N/A" only if
ALL [refunds] are "N/A"
Click to expand...

Did you try my suggestion?
--
Marsh
MVP [MS Access]


Jacob said:
I have a grouped textbox [totalRefunds] which is summing
the values of other textboxes, ie

=Sum([refunds])

Now, sometimes refunds are N/A, so what I need to do is this:

If ALL refunds state "N/A" then I want my [totalRefunds] to state "N/A"

Currently, if SOME refunds state "N/A" my [totalRefunds] textbox will
ignore them and Sum the rest.

I am just having trouble displaying "N/A" in the [totalRefunds] textbox
for when ALL [refunds] are "N/A", as it is currently producing "£0.00"
Click to expand...
"Marshall Barton" wrote
What is in the refunds field (not the text box that's
displaying the field) that indicates it is "N/A". Since
you're summing the field, it must be a numeric (Currency?)
type of field, which can only contain a number or Null.

If you're interpreting a Null in the field to mean "N/A",
then you could use the expression:

=IIf(Count(refunds) = 0, "N/A", Sum(refunds))
Click to expand...
Click to expand...
 
Back
Top