Convert sql statement to vba

[tminihan]

Hi everyone!

I have an summary query to get the gross pay for staring and ending
date. I copied the sql select statement from the query. I'm trying to
use it in vba code so I can get the values and put them into my form.

Problem:
I'm getting the error "Expected line number or label or statement or
end of statement."

Here's the code

"SELECT Sum(tPayrollData_Detail.GrossPay) AS SumOfGrossPay, " & _
"Sum(tPayrollData_Detail.EE401k)AS SumOfEE401k, " & _
"Sum(tPayrollData_Detail.EE401kCatchUp) AS
SumOfEE401kCatchUp, " & _
"Sum(tPayrollData_Detail.Roth401k) AS SumOfRoth401k, " & _
"Sum(tPayrollData_Detail.Roth401kCatchup) AS
SumOfRoth401kCatchup " & _
"FROM tPayrollData_Detail " & _
"WHERE tPayrollData_Detail.EmployeeID = " &
[Forms]![fDataEntry]![txtEmpID] & _
"AND tPayrollData_Detail.CheckDate Between #" &
nz([Forms]![fDataEntry]! [txtFYBegDate]) & _
"# And #" & nz(([Forms]![fDataEntry]![cmbCheckDate]-1) &
"#):"

Would someone please help?

Thanks in advance

 

[TC]

That's not even one whole statement!

Show us the code - meaning, whole statements, before and after where
the error occurs.

I assume you're putting that SQL statement into a string variable
before you excute it? If you aren't - do. Then debug.print the value of
that variable immediately before you execute it, and post the result
back here (along with the code).

HTH,
TC (MVP MSAccess)
http://tc2.atspace.com

 

[warrior]

Dim SQL2 As String
SQL2= "SELECT Sum(tPayrollData_Detail.GrossPay) AS SumOfGrossPay,
" & _
"Sum(tPayrollData_Detail.EE401k)AS SumOfEE401k, " & _
"Sum(tPayrollData_Detail.EE401kCatchUp) AS
SumOfEE401kCatchUp, " & _
"Sum(tPayrollData_Detail.Roth401k) AS SumOfRoth401k, " & _
"Sum(tPayrollData_Detail.Roth401kCatchup) AS
SumOfRoth401kCatchup " & _
"FROM tPayrollData_Detail " & _
"WHERE tPayrollData_Detail.EmployeeID = " &
[Forms]![fDataEntry]![txtEmpID] & _
"AND tPayrollData_Detail.CheckDate Between #" &
nz([Forms]![fDataEntry]![txtFYBegDate]) & _
"# And #" & nz(([Forms]![fDataEntry]![cmbCheckDate]-1) &
"#):"

Dim db2 As DAO.Database
Dim rec2 As DAO.Recordset
Set db2 = CurrentDb()
Set rec2 = db2.OpenRecordset(SQL1)

With rec
.MoveFirst
Me.txtYTDGrossPay = ![SumOfGrossPay]
Me.txtEE401k = ![SumOfEE401k]
Me.txtEE401kCatchUp = ![SumOfEE401kCatchUp]
Me.txtEERoth = ![SumOfRoth401k]
Me.txtEERothCatchUp = ![SumOfRoth401kCatchup]
End With

 

[warrior]

I can't debug because I get the error when I click out of the
statement.

 

[warrior]

I figured it out.

Dim SQL2 As String
SQL2 = "SELECT Sum(tPayrollData_Detail.GrossPay) AS
SumOfGrossPay, " & _
"Sum(tPayrollData_Detail.EE401k)AS SumOfEE401k, " & _
"Sum(tPayrollData_Detail.EE401kCatchUp) AS
SumOfEE401kCatchUp, " & _
"Sum(tPayrollData_Detail.Roth401k) AS SumOfRoth401k, "
& _
"Sum(tPayrollData_Detail.Roth401kCatchup) AS
SumOfRoth401kCatchup " & _
"FROM tPayrollData_Detail " & _
"WHERE tPayrollData_Detail.EmployeeID = " &
CLng([Forms]![fDataEntry]![txtEmpID]) & _
" AND tPayrollData_Detail.CheckDate Between #" &
Nz([Forms]![fDataEntry]![txtFYBegDate]) & _
"# And #" & Nz(([Forms]![fDataEntry]![cmbCheckDate] -
1)) & "#"
MsgBox SQL2

Dim db2 As DAO.Database
Dim rec2 As DAO.Recordset
Set db2 = CurrentDb()
Set rec2 = db2.OpenRecordset(SQL2)

With rec
.MoveFirst
Me.txtYTDGrossPay = ![SumOfGrossPay]
Me.txtEE401k = ![SumOfEE401k]
Me.txtEE401kCatchUp = ![SumOfEE401kCatchUp]
Me.txtEERoth = ![SumOfRoth401k]
Me.txtEERothCatchUp = ![SumOfRoth401kCatchup]
End With

 

[TC]

Just a tip: the MoveFirst statement in the last few lines of your code,
will fail witgh a runtime error, if the recordset does not return any
records. It's usually best to check for that, like this:

with rec
if .bof and .eof then
' no records.
else
.movefirst
...
endif
enbd with

HTH,
TC (MVP MSAccess)
http://tc2.atspace.com