MS Access database support - Converting SQL in Access

Author

Converting SQL in Access

tminihan@pskansas.com

2006-04-07, 11:32 am

I have a summary query in Access97. I look for staring and ending date
and sum the grosspay. I took the sql from the query and I'm trying to
convert it in VBA withing Access. I keep getting Error: Expected line
number or label or statement or end of statement. I know I'm missing
something, but I can't figure it out. Help?

"SELECT Sum(tPayrollData_Det
ail.GrossPay) AS SumOfGrossPay, " & _
" Sum(tPayrollData_Det
ail.EE401k)AS SumOfEE401k, " & _
" Sum(tPayrollData_Det
ail.EE401kCatchUp) AS
SumOfEE401kCatchUp, " & _
" Sum(tPayrollData_Det
ail.Roth401k) AS SumOfRoth401k, " & _
" Sum(tPayrollData_Det
ail.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) &
"#):"

Thanks in advance! :)

John Welch

2006-04-07, 11:32 am

A good way to check a SQL statement like this for errors is to do the
following:

dim strSQL as string
strSQL = "SELECT Sum(...." (Put your entire SQL statement there)
debug.print strSQL

Then hit ctl-G to see the immediate window and see how the string came out.
You'll probably want to put a break after that last line so it stops there.

The only thing I noticed on quickly reading your SQL is that you might need
a space before the AND

Do you have any GOTO or RESUME statements in your code? The "expecting line
num or label" makes me think you might and that that might be the problem.

good luck
-John
<tminihan@pskansas.com> wrote in message
news:1144422133.850508.12590@i39g2000cwa.googlegroups.com...
>I have a summary query in Access97. I look for staring and ending date
> and sum the grosspay. I took the sql from the query and I'm trying to
> convert it in VBA withing Access. I keep getting Error: Expected line
> number or label or statement or end of statement. I know I'm missing
> something, but I can't figure it out. Help?
>
> "SELECT Sum(tPayrollData_Det
ail.GrossPay) AS SumOfGrossPay, " & _
> " Sum(tPayrollData_Det
ail.EE401k)AS SumOfEE401k, " & _
> " Sum(tPayrollData_Det
ail.EE401kCatchUp) AS
> SumOfEE401kCatchUp, " & _
> " Sum(tPayrollData_Det
ail.Roth401k) AS SumOfRoth401k, " & _
> " Sum(tPayrollData_Det
ail.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) &
> "#):"
>
> Thanks in advance! :)
>

warrior

2006-04-07, 1:31 pm

Thank you for your response. I figured it out.

Dim SQL2 As String
SQL2 = "SELECT Sum(tPayrollData_Det
ail.GrossPay) AS
SumOfGrossPay, " & _
" Sum(tPayrollData_Det
ail.EE401k)AS SumOfEE401k, " & _
" Sum(tPayrollData_Det
ail.EE401kCatchUp) AS
SumOfEE401kCatchUp, " & _
" Sum(tPayrollData_Det
ail.Roth401k) AS SumOfRoth401k, "
& _
" Sum(tPayrollData_Det
ail.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 = !& #91;SumOfEE401kCatch
Up]
Me.txtEERoth = ![SumOfRoth401k]
Me.txtEERothCatchUp = !& #91;SumOfRoth401kCat
chup]
End With

Have a great day :)

Sponsored Links