Displaying a blank in a report column when currency value is zero?

[pelcovits]

How do I get Access to put a blank in a report column (but not
eliminate the record completely), when the field in question is
currency format, and the value is zero? My understanding is that null
is not permitted as a currency value.

thanks

[Bilk Whitey]

"pe*******@phys ics.brown.edu" <pe*******@phys ics.brown.edu> wrote:

How do I get Access to put a blank in a report column (but not
eliminate the record completely), when the field in question is
currency format, and the value is zero? My understanding is that null
is not permitted as a currency value.

Try using an in-line 'if' to generate a calculated field in the report's
query, something like:

MyNewField:IIf([MyField]=0,Null,[MyField])

This should give a null value there the field contains 0.

Regards,
Keith.
www.keithwilby.com

[fredg]

On 6 Jan 2005 07:25:49 -0800, pe*******@physi cs.brown.edu wrote:

How do I get Access to put a blank in a report column (but not
eliminate the record completely), when the field in question is
currency format, and the value is zero? My understanding is that null
is not permitted as a currency value.

thanks

Set the control's Format property to:
$#.00;-$#.00;""
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Robert Pelcovits]

Keith,

Thanks for your quick reply.

A further question about formatting here. Using your example for my
case, MyField is currency, MyNewField would be text or a number (to
allow null). Is that correct? But then doesn't the report need a field
in this column of a single format (which I want to be currency to
process the data which is nonzero most of the time)? More importantly,
shouldn't the formats agree in an iif statement, or is that not the
case?

thanks

Bob

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bilk Whitey]

Robert Pelcovits <pe*******@phys ics.brown.edu> wrote:

Hi Bob.

Using your example for my
case, MyField is currency, MyNewField would be text or a number (to
allow null). Is that correct?
The in-line 'if' should return either the contents of the currency field or
a null.
But then doesn't the report need a field
in this column of a single format (which I want to be currency to
process the data which is nonzero most of the time)?
I haven't actually tried what I suggested (it was really just a 'starter
for 10') but I would have thought that non-zero values would retain their
native format.
More importantly,
shouldn't the formats agree in an iif statement, or is that not the
case?

I see what you're saying, but an in-line 'if' tests to see if a value meets
certain criteria and uses the arguments accordingly.

Regards,
Keith.

[Paradigm]

You can set the format property for the currency on the report to display
different formats for positive, negative, zero and null entries. Check the
format statement. It is easy to set the null and zero values to display as
blank by entering "" in the relevant positions.
Alex
<pe*******@phys ics.brown.edu> wrote in message
news:11******** **************@ c13g2000cwb.goo glegroups.com.. .

How do I get Access to put a blank in a report column (but not
eliminate the record completely), when the field in question is
currency format, and the value is zero? My understanding is that null
is not permitted as a currency value.

thanks