SQL Syntax - Something is wrong

I keep getting a warning that something is wrong with my syntax in the SQL part of the code below:
Private Sub btnDeleteAssignment_Click()
'****Delete a record from the SubtaskAssignments table****

Dim frm As Form
Dim ctl As Control
Dim db As DAO.Database
Dim strSQL As String
Dim i As Variant

Set frm = Forms("f_SubTaskAssignments")
Set ctl = frm![lstExistingAssignments]
Set db = CurrentDb

For Each i In ctl.ItemsSelected

'***For future reference - Add chr(34) around text!

strSQL = "DELETE FROM SubTaskAssignment WHERE " & _
        "[TaskOrderID] = " & ctl.Column(0, i) & " AND [SubTaskID] = " & ctl.Column(5, i) & " AND [CompanyID] = " & ctl.Column(6, i) & " And [PersonID] = " & ctl.Column(7, i) & " And [ReportAs] = " & Chr(34) & ctl.Column(4, i) & Chr(34) & ")"
            
db.Execute strSQL, dbFailOnError

Next i

ctl.Requery
End Sub

Open in new window


I can't see it! It is probably right in front of me, but I have been looking at this code for over an hour and cannot, for the life of me, figure out what the problem is.

Can someone please take a look at tell me where the syntax is wrong? The error message says it is in the strSQL section, though it highlights the db.execute strsql, dbFailOnError.
MeginAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BullockQA Engineer IIICommented:
I don't know why you used the square brackets.
Try removing [ and ]
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
re
ctl.Column(0, i)   etc
Not sure you can reference the Column property of a List/Combo box in SQL.
You may need to set variables to those first ...

The brackets are fine ... if Control Name had spaces, the brackets are required anyway ... not necessary here but not really the issue.
0
Dale FyeCommented:
I believe it will be the ")" at the end of the line of code (removed below).  Personally, I like to break this out with one criteria on each line, and then print the sql string so I can view it in the immediate window

strSQL = "DELETE FROM SubTaskAssignment " _
       & "WHERE [TaskOrderID] = " & ctl.Column(0, i) _
       & " AND [SubTaskID] = " & ctl.Column(5, i) _
       & " AND [CompanyID] = " & ctl.Column(6, i) _
       & " And [PersonID] = " & ctl.Column(7, i) _
       & " And [ReportAs] = " & Chr(34) & ctl.Column(4, i) & Chr(34)
debug.print strSQL

Open in new window

Although you don't have to wrap the field names in [ ], since you have not included any spaces or reserved words, I generally do so to make it more readable.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

omgangIT ManagerCommented:
add the line shown.  It will output the SQL statement to the Immediate window so you can review it.  If you don't see the error post it up so we can take a look.
OM Gang

Debug.Print strSQL     '<------------ add this line
db.Execute strSQL, dbFailOnError
0
Dale FyeCommented:
Joe,

The references to the ctl.Column(x, i) are all explicitly defined when the SQL statement is created dynamically.

Dale
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well ... you can't do that in the Query designer ... so was thinking it might now fly here either.
I SWEAR I saw a beginning Left paren ... but I guess not. So I too would say the closing paren is the issue.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony BerenguelCommented:
try changing line 18 & 19 to:
strSQL = "DELETE * FROM SubTaskAssignment WHERE " & _
        "[TaskOrderID] = " & ctl.Column(0, i) & " AND [SubTaskID] = " & ctl.Column(5, i) & " AND [CompanyID] = " & ctl.Column(6, i) & " And [PersonID] = " & ctl.Column(7, i) & " And [ReportAs] = " & Chr(34) & ctl.Column(4, i) & Chr(34) & ")"

Open in new window

0
MeginAuthor Commented:
Wow! I left for lunch and came back to so many answers! Thank you!

Ends up that I needed to get rid of the last paren and add a quotation mark.

Thank you!!!!!!!!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Megin ... actually I should not get the Accepted answer, as it was Dale who pointed out the extra paren first ...
Just sayin' ...

mx
0
MeginAuthor Commented:
I apologize if that was bad.

I started reading through the answers and, once I found out which one worked, I gave credit to the responses that pointed that out. There were just so many responses!

Dale, I didn't mean to short you credit.
0
Dale FyeCommented:
No problem, Megin.  ;-)

Glad we were able to resolve your problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.