By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,188 Members | 832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,188 IT Pros & Developers. It's quick & easy.

Conditional display on a report

P: n/a
I have a report whose Record Source I set in code when clicking on a command
button on a form. This Record Source is a table that gets created by the
code. I have a textbox on the report whose Control Source is a field in
this table. The field type is currency. Everything works good so far.

When the value in the field is zero dollars $0.00, I want to conditionally
display "N/A" (not applicable) instead of $0.00 on the report depending on
the value in another field from this table.

I figure there's got to be an easy way from within the design view of the
report. I'm using Access 07.

Thanks,

Gord
Sep 7 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On Sun, 07 Sep 2008 22:31:24 GMT, Gord wrote:
I have a report whose Record Source I set in code when clicking on a command
button on a form. This Record Source is a table that gets created by the
code. I have a textbox on the report whose Control Source is a field in
this table. The field type is currency. Everything works good so far.

When the value in the field is zero dollars $0.00, I want to conditionally
display "N/A" (not applicable) instead of $0.00 on the report depending on
the value in another field from this table.

I figure there's got to be an easy way from within the design view of the
report. I'm using Access 07.

Thanks,

Gord

Set the format property of this control on the report to:
$ #,##0.00;- $ #,##0.00;"N/A"

In Access help, look up Format Property + Number and Currency Datatype
to learn why this works.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Sep 7 '08 #2

P: n/a
Try playing with the control's format property.

Here's an example.

$0.00[Blue];-$0.00[Red];"N/A"[Black];"N/A"[Green]

The first section is 0.
The next is < 0.
The next is 0.
The next is Null.

You can modify it. The colours are optional.

I would caution you that zero is not "N/A",
that NULL is not "N/A",
and that zero is not NULL
and that NULL is not anything.
Many errors occur when these truths are ignored.

"Gord" <x1******@telus.netwrote in news:0DYwk.1341$yS5.92@edtnps83:
I have a report whose Record Source I set in code when clicking on a
command button on a form. This Record Source is a table that gets
created by the code. I have a textbox on the report whose Control
Source is a field in this table. The field type is currency.
Everything works good so far.

When the value in the field is zero dollars $0.00, I want to
conditionally display "N/A" (not applicable) instead of $0.00 on the
report depending on the value in another field from this table.

I figure there's got to be an easy way from within the design view of
the report. I'm using Access 07.
Sep 7 '08 #3

P: n/a
Thanks guys, but I don't think I explained myself clearly enough.

When the value is zero I want to display either the zero "$0.00" or "N/A"
depending on the value in another field. The other field has to be referred
to somewhere in the expression. Where and how this might be done (if
possible) in the report's design view is what I need to know.

Gord
Sep 9 '08 #4

P: n/a
Thanks guys, but I don't think I explained myself clearly enough.

When the value is zero I want to display either the zero "$0.00" or "N/A"
depending on the value in another field. The other field has to be referred
to somewhere in the expression. Where and how this might be done (if
possible) in the report's design view is what I need to know.

Gord
Sep 9 '08 #5

P: n/a
On Tue, 09 Sep 2008 02:32:38 GMT, Gord wrote:
Thanks guys, but I don't think I explained myself clearly enough.

When the value is zero I want to display either the zero "$0.00" or "N/A"
depending on the value in another field. The other field has to be referred
to somewhere in the expression. Where and how this might be done (if
possible) in the report's design view is what I need to know.

Gord
Please include the relevant portion of any previous post when
responding. You may know what that previous post and reply was all
about, but I certainly don't without having to dig out the other
message.
For any reader who comes across this message, here is the previous
message with my reply.
*****************
I have a report whose Record Source I set in code when clicking on a command
button on a form. This Record Source is a table that gets created by the
code. I have a textbox on the report whose Control Source is a field in
this table. The field type is currency. Everything works good so far.

When the value in the field is zero dollars $0.00, I want to conditionally
display "N/A" (not applicable) instead of $0.00 on the report depending on
the value in another field from this table.

I figure there's got to be an easy way from within the design view of the
report. I'm using Access 07.

Thanks,

Gord

Set the format property of this control on the report to:
$ #,##0.00;- $ #,##0.00;"N/A"

In Access help, look up Format Property + Number and Currency Datatype
to learn why this works.
***************
You're right! You didn't explain yourself clearly. <g>
And you really haven't been very specific now.
What other field? What is the criteria to show $0.00 or N/A?
I'll have to give you a generic solution.

As the control source of an unbound control:
=IIf([FieldName] = 0 and [SomeOtherField] = something,"N/A","$0.00")

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Sep 9 '08 #6

P: n/a
Thanks Fred. That works just fine.

Gord
"fredg" <fg******@example.invalidwrote in message
news:19******************************@40tude.net.. .
On Tue, 09 Sep 2008 02:32:38 GMT, Gord wrote:
>Thanks guys, but I don't think I explained myself clearly enough.

When the value is zero I want to display either the zero "$0.00" or "N/A"
depending on the value in another field. The other field has to be
referred
to somewhere in the expression. Where and how this might be done (if
possible) in the report's design view is what I need to know.

Gord

Please include the relevant portion of any previous post when
responding. You may know what that previous post and reply was all
about, but I certainly don't without having to dig out the other
message.
For any reader who comes across this message, here is the previous
message with my reply.
*****************
>I have a report whose Record Source I set in code when clicking on a
command
button on a form. This Record Source is a table that gets created by the
code. I have a textbox on the report whose Control Source is a field in
this table. The field type is currency. Everything works good so far.

When the value in the field is zero dollars $0.00, I want to
conditionally
display "N/A" (not applicable) instead of $0.00 on the report depending
on
the value in another field from this table.

I figure there's got to be an easy way from within the design view of the
report. I'm using Access 07.

Thanks,

Gord


Set the format property of this control on the report to:
$ #,##0.00;- $ #,##0.00;"N/A"

In Access help, look up Format Property + Number and Currency Datatype
to learn why this works.
***************
You're right! You didn't explain yourself clearly. <g>
And you really haven't been very specific now.
What other field? What is the criteria to show $0.00 or N/A?
I'll have to give you a generic solution.

As the control source of an unbound control:
=IIf([FieldName] = 0 and [SomeOtherField] = something,"N/A","$0.00")

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Sep 10 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.