Sum calculated fields in VBA - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Sum calculated fields in VBA

Walter Hamilton

4 years ago

I've built a Search Form to search through all of our Active Programs.  It has 11 search criteria and the search feature works great.  The problem I'm having is summing the value fields.  The fields I have are:

Total Value:
Funded Value:
Invoiced:
Total Remaining
Funded Remaining
Burn Rate:

The Total Value and Funded Value work great.  I'm having an issue with the Invoice field because it's a calculated field.  I always get an error.

Is there a way to do this in VBA after the SQL statement has run and then just Sum(Invoiced) AS TotalInvoiced?  If so, where would I put it in the SQL statement?  Would it go in the RequeryForm() sub routine?  I've asked the question in the Forum but haven't received a respone.

	Walter Hamilton OP @Reply 4 years ago
I think I figured out how to do it. I used a combination of Sum and DSum in the footer. =Sum(DSum("Amount","tbl_Invoices","[ChargeCode_ID]=" & [ChargeCode_ID])) This seems to work. I still need to do the other calculated fields, but so far so good. Is there a better way to do this?

Adam Schwanz @Reply

4 years ago

Just FYI, posting multiple times doesn't speed up the answering, if anything it slows it down, as long as the previous thread is still active try to keep everything in one post. (Especially since you just "bumped" it before making a new post)

Old Post

Is this a continuous form then? The values in the footer update based on your search results? Is your Invoice field giving you the correct number? I'm skeptical that if it's a continuous form that it can find all the records ChargeCode_ID and make a correct number in the footer. Maybe you're just lucky but I always have crap luck with footer calculations LOL. If everything I said is correct about your setup, I would've just tooken the search string WHERE clause and put it into the DSUM function probably.

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

Next Unseen

New Feature: Comment Live View