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