Subtotal from Recordset

1. msw
Junior Member
Join Date
Dec 1969
Posts
6

## Subtotal from Recordset

I have an ASP app that stores user loginDate, loginTime and logoutTime in an Access db. It also uses DateDiff() to calculate and record totalTime they were logged in. <BR><BR>A report page takes a date range from the user and creates a recordset, which outputs all relevant data to an html table. This works fine so far...<BR><BR>What I need to do is get a subtotal of the totalTime field for every weekly interval... beginning every Tuesday at midnight no less!<BR><BR>Any ideas on how to accomplish this would be greatly appreciated.<BR><BR>Regards,<BR><BR>msw

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Subtotal=Subtotal+RS(totalTime)

Calculate it at the same time you display the records.<BR><BR>The first "week" starts whenever the report page starts.<BR><BR>As you get each record, you check to see if it is in a new week. If so you display the subtotal for the prior week and reset the subtotal to zero.<BR><BR>At the end, if subtotal is non-zero you report it.<BR><BR>

3. msw
Junior Member
Join Date
Dec 1969
Posts
6

## RE: Subtotal=Subtotal+RS(totalTime)

Thanks Bill... That did the trick.<BR><BR>Best wishes for 2002!<BR><BR>msw

4. msw
Junior Member
Join Date
Dec 1969
Posts
6

## RE: Subtotal=Subtotal+RS(totalTime)

Bill,<BR><BR>If you&#039;re still there, you helped me with this and it worked perfectly. This is the code...<BR><BR>do while not rsRecords.EOF<BR> subtotal = FormatNumber(RoundTo(subtotal + rsRecords("PaidHours")/60, .25),2)<BR> If weekDay(rsRecords("LoginDate")) = 3 Then<BR> Response.Write("&#060;TR&#062;&#060;TD COLSPAN=4 ALIGN=right&#062;&#060;STRONG&#062;Weekly Subtotal &#060;/STRONG&#062;&#060;/TD&#062;")<BR> Response.Write("&#060;TD ALIGN=right&#062;&#060;STRONG&#062;" & subtotal & "&#060;/STRONG&#062;&#060;/TD&#062;&#060;/TR&#062;")<BR> Response.Write("&#060;TR&#062;")<BR> subtotal = 0<BR> End If<BR> rsRecords.MoveNext<BR>loop <BR><BR>However, I&#039;ve noticed that when there are multiple records for my subtotal day (day 3 in this case), I get a subtotal line for each one. Could you suggest how I might overcome this and get the subtotal only after the last record for that day?<BR><BR>Thanks in advance.<BR><BR>msw

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## RE: Subtotal=Subtotal+RS(totalTime)

First off, do *NOT* do the FormatNumber stuff until you are ready to display the data! FormatNumber returns a string, so you are asking VBS to continual add a string to a number, getting a number, and then convert that to a string...over and over again. That said...<BR><BR>&#060;%<BR>...<BR>do while not rsRecords.EOF<BR>&nbsp; &nbsp; subtotal = subtotal + RoundTo(rsRecords("PaidHours")/60,.25)<BR>&nbsp; &nbsp; If weekDay(rsRecords("LoginDate")) = 3 Then<BR>&nbsp; &nbsp; &nbsp; &nbsp; rsRecords.MoveNext &#039; the trick!<BR>&nbsp; &nbsp; &nbsp; &nbsp; If rsRecords.EOF Then<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; nextDay = 3<BR>&nbsp; &nbsp; &nbsp; &nbsp; Else<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; nextDay = weekday(rsRecords("LoginDate"))<BR>&nbsp; &nbsp; &nbsp; &nbsp; End If<BR>&nbsp; &nbsp; &nbsp; &nbsp; &#039; if next day is not 3, this is last Tuesday record<BR>&nbsp; &nbsp; &nbsp; &nbsp; If nextDay &#060;&#062; 3 Then<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Response.Write "&#060;TR&#062;&#060;TD COLSPAN=4 ALIGN=right&#062;" _<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; & "&#060;STRONG&#062;Weekly Subtotal &#060;/STRONG&#062;&#060;/TD&#062;" _<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; & "&#060;TD ALIGN=right&#062;&#060;STRONG&#062;" _<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; & FormatNumber(subtotal) & "&#060;/STRONG&#062;&#060;/TD&#062;&#060;/TR&#062;"<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; subtotal = 0<BR>&nbsp; &nbsp; &nbsp; &nbsp; End If<BR>&nbsp; &nbsp; &nbsp; &nbsp; &#039; whether we displayed subtotal or not,<BR>&nbsp; &nbsp; &nbsp; &nbsp; &#039; we have already called MoveNext, so don&#039;t do it again!<BR>&nbsp; &nbsp; Else<BR>&nbsp; &nbsp; &nbsp; &nbsp; rsRecords.MoveNext<BR>&nbsp; &nbsp; End If<BR>Loop <BR>...<BR>%&#062;<BR><BR>Untested! Try it!<BR><BR><BR>

6. msw
Junior Member
Join Date
Dec 1969
Posts
6

## RE: Subtotal=Subtotal+RS(totalTime)

It worked... Thanks!<BR><BR>And thanks also for the FormatNumber tip.<BR><BR>msw

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## A goof in my code!

I did <BR><BR>If RS.EOF Then<BR>&nbsp; &nbsp; nextDay = 3<BR>Else<BR>...<BR><BR>OOPS! When EOF is reached, you need to set nextDay to any day *EXCEPT* Tuesday!<BR><BR>That will trigger the code to say "this is the last Tuesday record" and display the subtotal. If you leave the code as is, it won&#039;t.<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•