Sum MSAccess field quantities in a recordset from MSExcel

  • Thread starter Thread starter gab1972
  • Start date Start date
G

gab1972

Sorry if the subject sounded confusing. Here's what I'm trying to
accomplish.

I have an Access database named AvgDays. In that recordset, I have
the following columns:
permit_num
county
isitD
isitM
dist_days
maint_days
curr_location

dist_days and main_days have a number of days. isitM could contain a
letter, "M". This means that not all of the records have an M in the
field.

What I want to accomplish:
Search every record in the recordset AvgDays where isitM = "M"...when
found, I want to continuously sum maint_days...so that it searches all
records and sums the total number of days. So if there are 100
records and say 75 have the letter M in isitM, I want to sum that
value, move to the next record, add that number, and so and so forth.

Is this possible? Any help would be GREATLY appreciated.
 
What other info do you need? I would do it in Access if I knew how.
But I need to sum all the data in that field and then put that one
total number in a cell in Excel.


....this is just a snipet from my coding...

sMCode = "M"
str = "SELECT SUM(maint_time) AS TOTAL FROM AvgDays WHERE isitM = '" &
sMCode & "' AND curr_location <> '" & cl & "' AND county <> '" & cnty
& "' GROUP BY maint_time"
rs.Open str, cn, adOpenKeyset, adLockOptimistic
lRecords = rs.RecordCount
MsgBox lRecords

this just gets me a total number of records that match that criteria
(9). I want the total of all the numbers in those 9 records.

?????
 
something like this im guessing
With rs
Do While (Not .EOF)
Range("A2").Value = Range("A2").Value +
..Fields("TOTAL").Value
.MoveNext
Loop
End With
 
Thanks! That's exactly what I was looking for!!
something like this im guessing
With rs
Do While (Not .EOF)
Range("A2").Value = Range("A2").Value +
.Fields("TOTAL").Value
.MoveNext
Loop
End With
Click to expand...
 
Back
Top