Picking a value from a VBA table

[gmaster]

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)"



Now, my question is: how can i use VBA for picking the value in "Tot" and use it later?
As in: Total = 10 + Tot
Can i just write like this? Or there's another syntax?

[ranman256]

Dont use vba.
Create a query with this sql, THEN if you need to fill the result into a text box (or msgbox)

txtBox = Dlookup("[tot]","qsMyTotalQry")

(ok, the dlookup is vba, but dont use vba for queries, make a query"

[ssanfu]

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:

    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

[gmaster]

Thanks a lot guys! With your help i made it work!!