Recordset Help!

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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.
 
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)
 
Klatuu, thank you so much! You guys are awesome, that's why I always come
here for help! Thanks again, have a good one!!!

Klatuu said:
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.

MCyn said:
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?
Click to expand...
Click to expand...
 
Back
Top