First, terminology.
Hi guys, i have this VBA script that creates a table with some Counting:
strSQL = "SELECT Sum(C1 + C2 + C3) AS Tot"
strSQL = strSQL & "FROM (SELECT Count(Tbl1.Campo_1) AS C1, Count(Tbl1.Campo_2) AS C2, Count(Tbl1.Campo_3) AS C3 FROM Tbl1)"
This does not "create a table". It is a text string that would create a query... if there was more commands.
This is what the code might look like:
Code:
Dim rst As DAO.Recordset
Dim strSQL As String
Dim tot As Long
tot = 0
strSQL = "SELECT Sum(C1 + C2 + C3) AS Tot"
strSQL = strSQL & " FROM (SELECT Count(Tbl1.Campo_1) AS C1, Count(Tbl1.Campo_2) AS C2, Count(Tbl1.Campo_3) AS C3 FROM Tbl1)"
'open recordset
Set rst = CurrentDb.OpenRecordset(strSQL)
'check to see if there are records returned
If Not rst.BOF Then
tot = rs.Fields(0)
' or
' tot = rs!tot
End If
'other code
'clean up
rst.Close
Set rst = Nothing
End Sub