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

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

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
"pe*******@physics.brown.edu" <pe*******@physics.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
Nov 13 '05 #2

P: n/a
On 6 Jan 2005 07:25:49 -0800, pe*******@physics.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.
Nov 13 '05 #3

P: n/a
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!
Nov 13 '05 #4

P: n/a
Robert Pelcovits <pe*******@physics.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.
Nov 13 '05 #5

P: n/a
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*******@physics.brown.edu> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.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

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.