473,322 Members | 1,259 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Converting SQL in Access

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_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) &
"#):"

Thanks in advance! :)

Apr 7 '06 #1
2 1124
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
<tm******@pskansas.com> wrote in message
news:11*********************@i39g2000cwa.googlegro ups.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_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) &
"#):"

Thanks in advance! :)

Apr 7 '06 #2
Thank you for your response. 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

Have a great day :)

Apr 7 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
by: chicha | last post by:
Hey people, I have to convert MS Access 2000 database into mysql database, the whole thing being part of this project I'm doing for one of my faculty classes. My professor somehow presumed I...
5
by: Terry Bell | last post by:
I'm upsizing an Access database. Got the data converted, working on the front end, converting queries to views, but having trouble converting queries that use logical expressions like the...
1
by: gary | last post by:
I am interesting in converting all or at least any portions of a Access 2002 frontend.mdb and backend.mdb into VB.net. Are there any conversion tools for such a monstrous task? I am especially...
2
by: Rohit | last post by:
Hi, My organisation is using Access 97 and going to replace it with access 2000. Users have n number mdb files created in 97. After installation of Access 2000 I need to conver ALL the mdbs to...
32
by: robert d via AccessMonster.com | last post by:
I'm looking at converting DAO to ADO in my app. All of my DAO connections are of the following structure: Dim wsName As DAO.Workspace Dim dbName As DAO.Database Dim rsName As DAO.Recordset ...
4
by: gg9h0st | last post by:
i'm a newbie studying php. i was into array part on tutorial and it says i'll get an array having keys that from member variable's name by converting an object to array. i guessed "i can...
9
by: Terry | last post by:
I am converting (attempting) some vb6 code that makes vast use of interfaces. One of the major uses is to be able to split out Read-only access to an obect. Let me give you a simple (contrived)...
4
by: Bob Alston | last post by:
Anyone have experience with converting an access app from Jet database to Mysql? I am specifically looking for any changes I would have to make to my access forms, queries, modules, vba code, etc....
1
by: murraystewart | last post by:
I have spent days building a database in access 2003 and the found it needs to run on Access 97, I did the conversion but the form is not opening with an error message "you loaded a form or report...
28
by: Randy Reimers | last post by:
(Hope I'm posting this correctly, otherwise - sorry!, don't know what else to do) I wrote a set of programs "many" years ago, running in a type of basic, called "Thoroughbred Basic", a type of...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.