How do I get more than one field value from a query in vba?

[Rich8541]

Hello, I have a query that I want run from vba using the sql code. The query has 11 fields. For example, for contractor = 80 i want to get the ST, OT and DT and fill 3 variables in my code. I know how to set SQL result equal to one variable in my code but how do I extract values for 3 variables. Sorry to ramble as it's been about 10 years since I've used Access so it's coming back to me slowly, slowly.
Here's my sql from the query. I want to fill variables for ST, OT and DT given the contractor ID.

SELECT DISTINCT T1.ContractorID, Count(T1.WorkDate) AS CountOfWorkDate, Sum(T1.SumOfST) AS ST, Sum(T1.SumOfOT) AS OT, Sum(T1.SumOfDT) AS DT, [ST]+[OT]+[DT] AS TotalHours, DatePart("yyyy",[WorkDate]) AS ThisYear, [OT]*1.5 AS OT_annuityCalc, [DT]*2 AS DT_annuityCalc, [ST]+[OT_annuityCalc]+[DT_annuityCalc] AS AnnuityHours, tblContractor.Contractor
FROM T1 INNER JOIN tblContractor ON T1.ContractorID = tblContractor.tblContractorID
GROUP BY T1.ContractorID, DatePart("yyyy",[WorkDate]), tblContractor.Contractor;
Thank You,
Rich

 

[PHV]

Use a Recordset.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Rich8541]

OK, thanks. I'm trying this but it returns the same value of 80 for each variable and 80 is also the Contractor ID I'm using to identify the record.

Public Sub Calc_Pay()
    Dim rst As DAO.Recordset, strSQL As String
'    Dim strPath As String
    Dim ST As Integer
    Dim OT As Integer
    Dim DT As Integer
    Dim Cid As Integer
    

    
    
    
    Cid = Forms!frmMain.txtContrID
    

    strSQL = "SELECT DISTINCT T1.ContractorID, Count(T1.WorkDate) AS CountOfWorkDate, Sum(T1.SumOfST) AS ST," & _
    "Sum(T1.SumOfOT) AS OT, Sum(T1.SumOfDT) AS DT, [ST]+[OT]+[DT] AS TotalHours, DatePart('yyyy',[WorkDate]) AS ThisYear," & _
    "[OT]*1.5 AS OT_annuityCalc, [DT]*2 AS DT_annuityCalc, [ST]+[OT_annuityCalc]+[DT_annuityCalc] AS AnnuityHours, tblContractor.Contractor" & _
    " FROM T1 " & _
    " INNER JOIN tblContractor ON T1.ContractorID = tblContractor.tblContractorID" & _
    " GROUP BY T1.ContractorID, DatePart('yyyy',[WorkDate]), tblContractor.Contractor" & _
    " HAVING (((T1.ContractorID)=" & Cid & "));"

    


    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

    If Not (rst.BOF And rst.EOF) Then
        Do
            ST = rst(ST)
            OT = rst(OT)
            DT = rst(DT)
            
        rst.MoveNext
        Loop Until rst.EOF
    End If

        rst.Close
    Set rst = Nothing

End Sub

 

[dhookom]

I believe you would need:

   If Not (rst.BOF And rst.EOF) Then
        Do
            ST = rst("ST")
            OT = rst("OT")
            DT = rst("DT")
            
        rst.MoveNext
        Loop Until rst.EOF
    End If

What do you expect to happen if you have records from more than one year?

Duane
Hook'D on Access
MS Access MVP