Hello,

I have a report that needs to show a running total of a certain set of numbers. Below is an example of what I need to see on the report (the columns should be side by side but I don't know how to post it that way):

Field Length
16
16
50
50
30

Start
1
17
33
83
133


I need an expression/formula that I can put in a text box for the Start column. As you can see I have to start with 1 and then add the next field length plus the number 1 and then add the next field length to that and so on. I would appreciate any help anyone can give.

Assuming your starting point is always going to be 1, I have a 98% solution.

In your report's detail line, you need three text boxes.

The first text box is called txtFieldLength and it's control source is the field holding the Field Length.

The second text (Named txtRunSum1) box ALSO contains the Field Length for it's control source. But, if you go to the properties for this text box, and select the Data tab, you can set the Running Sum property to "Over Group". (By the way, the first text box has this property set to "No". Now if you run your report right now, you'll see that you get 16, 32, 82, 132 & 162 for results. Just ONE OFF of what you want in your second column. So, we set this text box's visible property to NO, and create a third text box.

The third text box contains the control source "=[txtRunSum1]+1".

Voila. Or, since this is a 98% solution, "voil." Why 98%? Because the running sum is off by one row. There's no 1 in the first row, and there's a trailing "163" on the last row that you don't show in your example.

Now, maybe this is good enough for you. In which case, great! If not, maybe one of my compatriots here on UA can offer the last 2%.

If it ABSOLUTELY has to work out like you're describing, I do have a function that can pull data out of the previous record, but implementing it is a lot more work than I just described above for an otherwise simple-looking report.

Dennis

Thanks, I will give your solution a shot. There is actually more data on the report. The Start column was the only problem. I don't know if this will help or not but I have a function called Documenter that creates a table called TableInfo that contains the following fields (which I have on my report):

FieldNumber (an auto number)
FieldName
FieldType
FieldSize (which is my Field Length)

The Documenter creates the TableInfo table based on a "Main" table that I have imported, thus the report I am trying to create displays the documenter info for the "Main" table. The only piece missing is the Start column. Just thought I might add a few more pieces to the puzzle. Appreciate the help!

And this ABSOLUTELY does need to work the way I have described it.

Fair enough. Here's a link to a Microsoft Knowledge Base article that shows how you can refer to previous or next records with a custom function they provide:

http://support.microsoft.com/kb/Q101081/

Basically, once you've installed the function the article describes, you can change the record source for that second text box slightly so that it reads the previous record's value, rather than the current one. That should fix the one-off problem. From there, the biggest issue will probably be that "1" at the top of the second column.

Once you have the function in place and working properly, The function will return a zero for the first value (since there is no previous record on the first record). A simple if test should straighten that out.

Don't be intimidated by the complexity of the article. It's a very powerful tool once you get to know it. I've adapted it for use in a running average that goes across the previous FOUR records... and I once thought would be impossible in Access.

Dennis

Thanks doctor9!! You've been a big help.

Hey doctor9,

I'm getting myself lost on this function. I am trying to modify it so that it is looking for the records in my TableInfo table, I am not using a form. I changed the F as Form to T as Table and the Set RS = F.RecordsetClone to Set RS = T.RecordsetClone. When I compile I get Method or data member not found and it points to .RecordsetClone. I'm sure there are more errors because I was unsure of what to use for KeyName or KeyValue. I hate to keep bugging you, but could you throw me a couple more pointers. I am still pretty green at the code thing.

Let's see... I'm poring over the database I ended up using the utility for... I modified the function pretty heavily by the time it was working, but here's the gist of what you'll need to do.

(First, let me state that I'm assuming you read the article, and decided to go with the "Using Code" section rather than the "Using DLookup" section, since that's what I ended up doing myself. If this is incorrect, most of the following will be less useful than I'm hoping. I was quietly hoping that your table design would allow you to use the simpler DLookup procedure.)

I made the function work with QUERIES instead of based on FORMS so I could adapt it to reports. So, I removed the argument "F as Form" from the first line.

The original PrevRecVal() function has this line:

' Get the form recordset.
Set RS = F.RecordsetClone

I replaced that with code that assembles an SQL statement into a string, then assigns that string to the recordset RS.

What I ended up doing was creating my base report (without the second column of displayed data, in your case). I then copied the SQL statement used to create the report (paying special attention to any sorting and filtering). I pasted the SQL statement into the function as a long comment field, then pieced it back together into a text string. (Why do I do it this way? Just for readability. It could, in theory, be one LOOOONG line with "strSQL=" at the beginning, running hundreds of characters to the right. But it's a pain to read.)

strSQL = "SELECT [tblGradDataEntry].[GADataLineID], [tblGradDataEntry].[WtRet1],"
strSQL = strSQL & "[tblGradDataEntry].[WtRet2], [tblGradDataEntry].[WtRet3],"
'...Skipping a bit here...
strSQL = strSQL & "[tblGradDataEntry].[WtRet12], [tblGradDataEntry].[SampleWt]"
strSQL = strSQL & " FROM [tblGradDataEntry]"
strSQL = strSQL & " WHERE ((([tblGradDataEntry].[GradationID]) = 3"
strSQL = strSQL & ")) ORDER BY [tblGradDataEntry].[GADataLineID];"

Set RS = db.OpenRecordset(strSQL, dbOpenDynaset)

What the query does isn't important to you; the key is that I am creating a REPLICA of the query that's driving the report. The most important items to appear in the SQL statement are the fields that determine the sort order, any filtering criteria and the fields that need to be back-referenced to previous records. For the sake of simplicity, I'd include the entire report's SQL to avoid any surprises while working things out.

So, now if I look in my report query in design view, I've got fields like this:

PP1: 100*(1-[WtRet1]/[DryWt])
RunAvg1: QRunAvg4("GADataLineID",[GADataLineID],"WtRet1")

Sorry I don't have much time to dedicate to you today; just got a new printer today, and I'm installing/configuring/etc.

Hopefully these bits will help you futher along the way.

Dennis

Thanks so much for your time!

Hello doctor9,

I am still fussing with the code but I just can't figure some things out. I know I am going to sound really dumb here, but, after I copy the code and modify it, what do I do then? When I try to run it I get prompted for a macro and I have no idea what to enter for the macro. Should I not try to run it and just use a formula referring to the function in the text box on my report? If so, what formula do I use? I tried using the example formula that came with the code and then modified it, but it didn't work. I am really lost here, I hope you can help today. This is like the last major thing I have to figure out and I am done with creating this special database for customer orders for my department. I guess you can tell I'm not a programmer by any means. I am basically teaching myself all about Access. I get stumped when it comes to the code. Thanks for any help you can give.