Hi all,

I would like to add a column to this query that calculates a running total of the 'This Invoice' field.

Here is the query design :

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=24115&stc=1&d=1225284518

This query will return results based on information provided by the user.

Here is the result if I type in 001 as the project number I would like to look at:

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=24116&stc=1&d=1225284698

I have attempted to use the DSum function. It works fine however, it completly ignores the parameter criteria of the query and returns a running total of the entire 'Invoices' table. I would like it to return a running total of only the figures relating to the project number I asked it to look at.

Here is the code I used:

RunTot: DSum("ThisInvoice","Invoices","[InvoiceNumber]<=" & [ID] & "")

I would like the 'RunTot' calculated field to return results like this:

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=24114&stc=1&d=1225285035

What do I need to add to restrict the calculation to the results of the query?

You have to go via Function.
Look at "DemoRuningtotalA2000.mdb" (attachment, zip).
Look at Table1, query1, Form1 (VBA), Module1.
Open Form1 and try.
I suggest make a report on query1.

Thanks for that.

The example you have given me works great however, when I recreate the query using the field names of my table it fails miserably.

Is there any reason why I cannot simply add to the criteria within the DSum function I have already created? It currently produces a running sum of the entire Invoices table. All I need it to do is only RunSum the values produced by the query it exists in.

accessjockey

It must be you made a mistake somewhere.

Hi,

Yes you're correct. I didn't export the module to my databse. Once I did that it worked. However!

The running total just keeps on adding to the last result of the query. Every time I fire up the query the running total adds the results of the last query to one I have just generated. Even if it is a totally different job number. Not good accounting practice.

This is getting way to difficult for such a simple piece of arithmetic. All I need is the sum of one particular field up until the current date for one particular project.

The running sum works great within a report however this function falls down when the group of records are not the same job number. For example, a report that looks at invoices that are 30days overdue.

The current Dsum function I have already created (see above) calculates the running total fantastically. It just needs to be given a further criteria restricting it to the project number that is given by the user when the query is run.

Why is that such a difficult thing?

You have said;
"The running total just keeps on adding to the last result of the query. Every time I fire up the query the running total adds the results of the last query "

Look at Form1 VBA, i think you omited A2 = 0 code.