Use of Nz statements for 3 different arguments

[MNNovice]

I tried to browse through several postings on Nz and iif statements. Coudln't find one that matches what I am looking for.

To calculate "Balance" there are 3 different situation

1. Balance = [Authorized] - [Expensed]

2. For balance where[Expense] = 0 I used
Balance: ([Authorized] - Nz([SumOfExpense],0))

3. For Expense is less than 0 (a negative due to overspending) is where I got stuck. When I tried iif statement it skipped where Expense has a zero value.

How do I capture all three situations in one function? Can I use a Nz statement to calculate Balance for all three situations? What I mean is to calculate Balance 1)iff Expense is Zero, do this, if Expense is less than Zero, do this, Otherwise Authorized less Expense equals Balance.

Can someone please help me with this?

Many thanks in advance.

[NeoPa]

Your question leaves a number of ambiguities about what you are dealing with.

From the name of [SomOfExpense] it seems clear that :

1. The name of the field is [Expense] rather than [Expensed] as indicated in point #1.
2. You are working across multiple records rather than within any particular record.

In that case I expect you'll need the code you have in point #2 for all circumstances - assuming you have :

SumOfExpense: [Authorized]-Sum([Expense])

Alternatively, the following should always work :

Balance: [Authorized]-Nz(Sum([Expense]),0)

Of course, you do understand that for this to work as you have it you must GROUP BY [Authorized].

Frankly, this would all be much clearer if we knew more precisely what we were working with. Sometimes suggesting an answer based on guesswork can be more misleading that helpful.

[MNNovice]

NeoPa,
Sorry for all the confusions caused by my inability to explain things better.

I have a query to calculate a balance amount which is basically the difference between authorized amount ([Authorized])and expensed amount ([Expense]). This query is based on two other queries: one has the [SumOfExpense] and other query has the [Authorized].

Balance = [Authorized] - [SumOfExpense]

I am having problem coming up with the correct balance where [SumOfExpense] is either zero or a negative number. When I use Nz statements I get the correct balance where [SumOfExpense] is zero. But I cannot find the right function to get to the correct Balance incorporating all 3 situations, i.e., [SumOfExpense] is either zero or a negative or a positive number. If it's a negative number, for example, if authorized amount was $100 and someone spent $300 then the balance should be (200) but i get $100 - (300) = 400.

Hope this explains my hopeless situation. If not, let me know. Thanks again.

[NeoPa]

This still doesn't make sense Mareena.

On the one hand you say [SumOfExpense] reflects the total of all expenses. In this case it should be $300.

On the other you say the value for [SumOfExpense] is -$300 (or (300)).

If it is true that the value in [SumOfExpense] is actually (300) then the problem is not with this formula but with the values coming through to it. If the value desn't represent the actuality then the problem is right there - with the data coming in.

If, on the other hand, you are saying that you need to treat all values as positive regardless of whether they are positive or negative (And I'm guessing here because you haven't even hinted at that yet.) then we can handle that quite easily :

Balance: [Authorized]-Abs(Nz(Sum([Expense]),0))

Abs() returns the absolute value of any number passed. IE. It turns negative values into their positive equivalents.

If none of this helps then I'm going to need to question you to see if I can work out what it is we're actually dealing with.

[MNNovice]

What I meant is that when the authorized amount was $100 but the expense total was $300 then they have overspent by $200. So the total should be a negative $200. In this example the expense amount is a negative number (over the budget - they will get $100 that was authorized and will be in a hole for the balance i.e., negative $200. This would tell a manager that they will need to transfer funds from another expense account to make the authorized amount higher (in the event they have more expenses to come) or equal to $300.

What perhaps I didn't get to explain is that [SumOfExpense] = [Authorized] - [Expense]

-200 = 100 - 300

Hope this helps. Thanks.

[NeoPa]

MNNovice:
What perhaps I didn't get to explain is that [SumOfExpense] = [Authorized] - [Expense]

If this means what I think it means then you can't sensibly use [SumOfExpense].

I need to talk with you later to see if we can make some sense of this together. I'm afraid what is here so far consists of very little that makes sense and much that seems contradictory. I'm struggling even to guess what we might be dealing with.

Please excuse me if I sound like I'm criticising you. I don't mean to. I'm just explaining why I can't help as things stand. On the other hand, I'm sure we can get to the bottom of things quite quickly and easily once we know what it is we're dealing with.