Formatting and functions in reports

[Isa]

DiHi,

I have 2 questions:

One is on formatting. I have a formula and would like the negative results
to be in brackets rather than with a minus sign. The formatting I normally
use in Excel is:
#,##0;[Red](#,##0) . How can I make this work in a report in Access?

Second is formulas. I would like to enter a type of IF Statement so that
when my function tries to divide a value by zero it will return a dash sign
(-) instead of #Div/0!

Hope you can help!
Thanks.

 

[John Spencer]

You should be able to apply that format to the control on the report that is
displaying the value.

Formula
=IIF(X = 0,"-",Y/X)

Problem with this is that it will always return a string. So you might want to use
IIF(X = 0,NULL,Y/X)
and then apply a format to the control to show the dash when the value is
null. For numbers a format can have four parts - positive, negative, zero,
and Null. I think that entering the following as the control's format
property will give you the expected results.
#,##0.0;[#,##0.0];0;-

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County