Recordset Help!

[Guest]

Hello, I am in need of some help. I am using Access 97 and I am currently
building a module. The module is looking at a table (name:14_Table) and
pulling certain criteria and placing the data into a template in excel. The
criteria I am pulling is using a OpenRecordset function: (my code example)
Dim rst As Recordset

strSQL = ("SELECT * FROM 14_Table WHERE FACILITY_GROUP = 'Baptist'
AND" & " LAG >=0 And LAG <=15")
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.RecordCount = 0 Then
.Range("C23").Value = "0"
Else
rst.MoveLast
.Range("C23").Value = rst.RecordCount
EndIf

Ok, that is how I am pulling criteria for a count of total records and
placing them in a excel template. Now the question is, how can I create
another recordset type code to pull the SUM of the field "TOTAL_BILLED" from
those records the above code pulls??? Any ideas?

 

[Guest]

lngTotBilled = nZ(DSum("[TOTAL_BILLED]", "14_Table", "[FACILITY_GROUP] =
'Baptist' AND LAG BETWEEN 0 AND 15"),0)
Note the Dsum surrounded by the Nz function. That is so if no records match
the criteria and Null is returned, it will be converted to 0.

 

[Guest]

BTW, you could also use the DCount function instead of a recordset to do what
you are doing.

.Range("C23").Value = Nz(DCount("*", "14_TABLE", _
"[FACILITY_GROUP] = 'Baptist' AND [LAG] BETWEEN 0 AND 15"),0)

 

[Guest]

Klatuu, thank you so much! You guys are awesome, that's why I always come
here for help! Thanks again, have a good one!!!

lngTotBilled = nZ(DSum("[TOTAL_BILLED]", "14_Table", "[FACILITY_GROUP] =
'Baptist' AND LAG BETWEEN 0 AND 15"),0)
Note the Dsum surrounded by the Nz function. That is so if no records match
the criteria and Null is returned, it will be converted to 0.

Hello, I am in need of some help. I am using Access 97 and I am currently
building a module. The module is looking at a table (name:14_Table) and
pulling certain criteria and placing the data into a template in excel. The
criteria I am pulling is using a OpenRecordset function: (my code example)
Dim rst As Recordset

strSQL = ("SELECT * FROM 14_Table WHERE FACILITY_GROUP = 'Baptist'
AND" & " LAG >=0 And LAG <=15")
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.RecordCount = 0 Then
.Range("C23").Value = "0"
Else
rst.MoveLast
.Range("C23").Value = rst.RecordCount
EndIf

Ok, that is how I am pulling criteria for a count of total records and
placing them in a excel template. Now the question is, how can I create
another recordset type code to pull the SUM of the field "TOTAL_BILLED" from
those records the above code pulls??? Any ideas?