Calculation issue in a query

[Collins]

This is so simple but it isn't working so I'm missing something obvious. I created a query with a deposit field for the month of January. In that same query I have a debit field. I created a second query that sums the deposit and the debit column. I then created a field that took the deposit sum and subtracted the debit sum for a total amount left. But when I run the query the deposit field and debit field are correct but the amount left is empty.

What am I doing wrong?

I'll try to post a pic.

[JoeM]

In your calculation field, change the "Total" row value from "Group By" to "Sum" also.

[Collins]

In your calculation field, change the "Total" row value from "Group By" to "Sum" also.

Tried it... the field is still blank.

[JoeM]

Are your Transaction and Deposit fields populated with numbers for ALL records?
Also, get rid of the space in your field name, i.e. use Left_Over.

[Collins]

Are your Transaction and Deposit fields populated with numbers for ALL records?
Also, get rid of the space in your field name, i.e. use Left_Over.

No. I have 31 records. 2 are deposits and the rest are debits. So 29 or the deposit records are blank and 2 of the debit records are blank.

I removed all spaces in my calculation. Access added on space back and I changed Left Over to Left_Over.

The field is still blank.

[JoeM]

Sometimes, Access chokes if you try to do too much in one query.

However, let's try this. Try changing your calculation to this:
Left_Over: Nz([Transaction_Deposit],0) - Nz([Transaction_Debit],0)

[Collins]

Sometimes, Access chokes if you try to do too much in one query.

However, let's try this. Try changing your calculation to this:
Left_Over: Nz([Transaction_Deposit],0) - Nz([Transaction_Debit],0)

Okay the image I posted was the complete Query. To me that's a pretty simple Query and a pretty simple calculation.

With that said you formula worked. Why? Also... I'm just learning this stuff so... be gentle...

[JoeM]

You must account for all the records with Nulls in your query. Note that Access doesn't always do things in the order you expect. Sometimes, it tries to do calculations BEFORE applying any criteria that you have. So, even if you are filtering out records with Nulls, they may still mess up your calculation.

The Nz function handles Null values. It tells you want to return in case of a null. We cannot do Sums with Nulls, so we tell it to return a 0 in its place.
See this for details on the Nz function: http://www.techonthenet.com/access/f...dvanced/nz.php

Hope that helps!

[Collins]

You must account for all the records with Nulls in your query. Note that Access doesn't always do things in the order you expect. Sometimes, it tries to do calculations BEFORE applying any criteria that you have. So, even if you are filtering out records with Nulls, they may still mess up your calculation.

The Nz function handles Null values. It tells you want to return in case of a null. We cannot do Sums with Nulls, so we tell it to return a 0 in its place.
See this for details on the Nz function: http://www.techonthenet.com/access/f...dvanced/nz.php

Hope that helps!

It helps a ton Joe thanks.

[Collins]

Here's a question Joe. Is it possible to make the Left_Over field distinguish between a negative value and a positive value. For example if the debits are more then the deposits the value in the field would be negative. And then if you really wanted to impress me can a negative number be shown in red and a positive number be shown in green?

[JoeM]

It should already return a negative if your total debits are more than your total deposits, as you would be subtracting a bigger number from a smaller one.

As for coloring the text, you can use Conditional Formatting on Access Forms or Reports (but not Queries).
See: https://support.office.com/en-sg/art...5-44e43b05e22f
and http://www.techrepublic.com/blog/mic...s-and-reports/

[ssanfu]

<snip>

As for coloring the text, you can use Conditional Formatting on Access Forms or Reports (but not Queries).
</snip>

Try this in the query.
In design view of the query, right click in the column "Left_Over".
Click on properties (bottom of the list)

In the FORMAT row, enter

$#,##0.00[Green];($#,##0.00)[Red]

Close the properties dialog window.
Execute the query.

[JoeM]

Try this in the query.
In design view of the query, right click in the column "Left_Over".
Click on properties (bottom of the list)

In the FORMAT row, enter
Code:
$#,##0.00[Green];($#,##0.00)[Red]
Close the properties dialog window.
Execute the query.

Clever. I stand corrected!

[Collins]

It should already return a negative if your total debits are more than your total deposits, as you would be subtracting a bigger number from a smaller one.

As for coloring the text, you can use Conditional Formatting on Access Forms or Reports (but not Queries).
See: https://support.office.com/en-sg/art...5-44e43b05e22f
and http://www.techrepublic.com/blog/mic...s-and-reports/

Okay... Rather than a negative my value comes back () on either side of it.

[Collins]

Try this in the query.
In design view of the query, right click in the column "Left_Over".
Click on properties (bottom of the list)

In the FORMAT row, enter

$#,##0.00[Green];($#,##0.00)[Red]

Close the properties dialog window.
Execute the query.

You guys are the masters of disaster!!!! I love this forum!!! That is so cool...