Calculation Query not showing - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Calculation Query not showing

Robert Freeman

3 years ago

I am trying to total fields in a row using a query. I have been following Richard's invoicing video and as far as I can see I have the correct expression. However, when you run the query it correctly shows the new column but no data.
The query calculation is: TotalDue: [SubsPaid]+[OtherDonations]
All the fields are formatted as currency. Sometimes there is no value in one of the fields. It does not show a result even if there is a value in each of the fields.
Can someone please give me a pointer as to what I have done incorrectly. Thanks.

	Kevin Robertson @Reply 3 years ago
Screenshots?

	Robert Freeman OP @Reply 3 years ago

	Robert Freeman OP @Reply 3 years ago

	Juan C Rivera @Reply 3 years ago
try this and see TotalDue: = sum([subspaid] + [OtherDonations]) Let me know if it works. V/r Juan

	Kevin Robertson @Reply 3 years ago
It looks like you may have inadvertently hidden the last column (set its width to zero (0)). If you hover over the right edge of the last column until you see a cursor similar to the one below you should be able to resize the column. Have you tried rebuilding the Query?

	Kevin Robertson @Reply 3 years ago

	Robert Freeman OP @Reply 3 years ago
Sorry, I sent the screenshot of the table and not the query result. I'll upload it again now. I have already tried the expression suggested by Juan and get the following error: 2Your query does not include the specified expression 'Payment_ID' as part of an aggregate function.

	Robert Freeman OP @Reply 3 years ago

	Kevin Robertson @Reply 3 years ago
OK. This is because OtherDonations is Null. Use the NZ Function. You can also use the CCurr function to convert the result to a currency. `TotalDue: CCurr(Nz([SubsPaid]) + Nz([OtherDonations]))`

	Robert Freeman OP @Reply 3 years ago
Kevin, as always, thank you so much. I would never have gotten to that expression. Incidentally, it brings up an error (Undefined function'CCurr' in expression) with the 'CCurr' in the expression but works fine without. Could that be because the field is already defined as 'currency' as in Richard's video?

	Kevin Robertson @Reply 3 years ago
Apologies. Heavy fingers while typing. LOL. Should be: CCur

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View