Writing SQL in VBA

[LaBam]

I'm trying to write a query in VBA to sum and count salaries and persons based on dates on a form. But I keep getting SYNTAX ERROR. Could someone please help me write this in the correct form it should be.

Here is my code:

Set rstOutGoingMin = "Select AllNames, Sum(Salary) As TotalSalary, Count(AllNames) As TotalCount From REVENUE " _
    & "Where StartDate >= '" & [Forms]![frmRevenue]![txtBegin] & "'  " And StartDate <= '" & [Forms]![frmRevenue]![txtEnd] & "'" & "GROUP BY AllNames ; "

 

[bigstav]

Corrected SQL

Presuming you are assigning the Variable as a String to the SQL Statament, the following will work :-

Dim rstOutGoingMin As String

rstOutGoingMin = "SELECT AllNames, SUM(Salary) AS TotalSalary, COUNT(AllNames) AS TotalCount FROM REVENUE " & _
" WHERE StartDate >= '" & [Forms]![frmRevenue]![txtBegin] & "' AND StartDate <= '" & [Forms]![frmRevenue]![txtEnd] & "' GROUP BY AllNames;"


NB :- Don't use "Set rstOutGoingMin" as Set is used for Objects, not Variables.

Hope this helps.

 

[workmad3]

if rstOutGoingMin is a dao.recordset (which I guess it is), you want to do

set rstOutGoingMin = CurrentDB.OpenRecordset("SELECT AllNames, SUM(Salary) AS TotalSalary, COUNT(AllNames) AS TotalCount FROM REVENUE " & _
" WHERE StartDate >= '" & [Forms]![frmRevenue]![txtBegin] & "' AND StartDate <= '" & [Forms]![frmRevenue]![txtEnd] & "' GROUP BY AllNames;")

Hope that helps

 

[LaBam]

Big thank yous to both WORKMAD3 and BIGSTAV. It worked perfect!