Negative Number Issue

[HezMac]

Hi

I'm trying to populate a payment field. I have a list of all 12 months, and the report is based on a date range. The report is based on fiscal year, April 1 - March 31.

If the payment is within the date range and is not zero, I want the number value to show, either negative or positive.

If the payment is within the date range and is zero, I want a zero to show.

For the months outside the date range, I want a '-' to show.

My problem is this: When the payment is negative (a void or NSF payment) the number shows in parenthesis (800) when I want it to show up as -800.

If I take out the ToText, the formula doesn't work.

if {Query.MARCH} <> 0 then
    ToText({Query.MARCH})
else if 3 < month({?StartDate}) and 3 < month({?EndDate}) and year({?StartDate}) <> year({?EndDate}) then
    "0"
else if 3 < month({?StartDate}) and year({?StartDate}) = year({?EndDate}) then
    "-"
else if 3 > month({?EndDate}) and 3 < month({?StartDate}) and year({?StartDate}) <> year({?EndDate}) then 
    "-"
else if 3 > month({?EndDate}) and year({?StartDate}) = year({?EndDate}) then 
    "-"
else "0"

Thanks for any suggestions.

 

[synapsevampire]

Your stating architecture when you should be describing the environment and requirements.

Your formula isn't required at all.

Change the default for the numeric field in Crystal y selecting formula->Options->Fileds->Number (assuming it's a numeric and not money, again, post environment and requirements) to default to a negative value display.

Or to cheat your current formula, use:

if {Query.MARCH} > 0 then
ToText({Query.MARCH})
else if {Query.MARCH} < 0 then
replace(replace(ToText({Query.MARCH}),"(","-")")","")
else if 3 < month({?StartDate}) and 3 < month({?EndDate}) and year({?StartDate}) <> year({?EndDate}) then
"0"
else if 3 < month({?StartDate}) and year({?StartDate}) = year({?EndDate}) then
"-"
else if 3 > month({?EndDate}) and 3 < month({?StartDate}) and year({?StartDate}) <> year({?EndDate}) then
"-"
else if 3 > month({?EndDate}) and year({?StartDate}) = year({?EndDate}) then
"-"
else "0"

-k