Please advise what I am doing wrong and how to correct this. Basically within a form I have a field that provides a calculation of current month vs previous months worth of business. The current month is a negative balance vs the previous month which is a positive balance. I pull the information from a 2 queries that establishes a sum total for the current months volume and another query that provides the volume sum total from the previous months. The totals are appearing on the form without issue. It is when I go to calculate the percentage between the two amounts that I am having the issue with.

Here is my formula:

=IIf(Nz([Text52])=0,0,NZ([GrossTotals]+[Text52])/[Text52])

Gross Total (current month) This is my negative amount
Text 52 (Previous month) This is my positive amount.

-3,404,663,743.66 (Current)
12,367,534,803.94 (Previous)
My percentage is not looking at the current as a negative number (I believe) =8962871060.28/12367534803.94=.72% It should actually be a -.72

Please advise how I get around this. I am truly baffled. I do not want to go and have to manually fix this every month. The database should do this automatically.
Thanks for your help.

Could you clarify your reasoning for your logic (total / part)? Usually to calculate a percentage the logic is (part / total).

Previous Month =IIf(Nz([Text52],0)=0,0,[Text52]/ ([Text52] + NZ([GrossTotals],0)))
Current Month =IIf(Nz([Text52],0)=0,0,[GrossTotals]/ ([Text52] + NZ([GrossTotals],0)))

two month total = 8,962,871,060.28

Previous Month % = 138%
Current Month % = -38%

Basically when I created that formula, it was based on an Excel spreadsheet. That was how it was done previously. The formulation you have above is for each field % that is not what I am doing. I take the actual sum total of the current(GrossTotals)/previous(Text 52) which then = the percentage.

Example: Current field -3,404,663,743.66 Previous field 12,367,534,803.94 Then I have the Percentage amount: =IIf(Nz([Text52])=0,0,NZ([GrossTotals]+[Text52])/[Text52])

I think I am confused because of your field names/description/textbox names. Current month = [GrossTotal] = "current field", PreviousMonth = [text52] = "previous field". is this correct?

your Percentage Amount formula results:
Order of Operations:
1) 12,367,534,803.94 + -3,404,663,743.66 = 8,962,871,060.28
2) 8,962,871,060.28 / 12,367,534,803.94 = .72

your formula IS looking at the CurrentMonth as a negative.

I think you still need to look at the logic of your formula. If you are trying to get a percent of the current month's progress in $'s of sales compared to the previous month's total $'s in sales look at this example:
previous month :$10,000
Current Month: $5,000
Current Month Sales Progress: 50% or [currentMonth] / [PreviousMonth]

If you want your percentage to equal 100% when the [current month sales] = [Previous month sales], then this would be your formula: IIf(Nz([Text52],0)=0,0,NZ([GrossTotals],0)/[Text52])

If this is still not helpful would you please provide a detail description of your percentage. your comment "this is how it was done previously" does not help me at all....
thanks.

I tried your IIf(Nz([Text52],0)=0,0,NZ([GrossTotals],0)/[Text52]) ....I got all kinds of errors flagged. Ok.If I use your formula you are actuall saying as follows:

IIf(Nz([Previous Month],0)=0,0,NZ([Current Month],0)/[Previous Month])

However, basically what you are telling me with this formula is don't divide the Previous Month into the end result of current month - previous month. So, I tookk my original formula and removed the previous month and here is what I now have....which worked!

=IIf(Nz([Text52])=0,0,NZ([GrossTotals]+[Text52))

Thanks for your help, it is greatly appreciated.

the formula you posted above is adding [grossTotals] and [text52], which should result in 8,962,871,060.28.

This is NOT what I was telling you. I was saying do NOT add the current month and previous month. you need to DIVIDE the current month by the previous month.