Error: Syntax Error (Missing operator) in query expression

I am getting the following error every time I try to run a particular piece of code:
Run-tim error '3075' Syntax error (missing operator) in query expression

The code is below.

I am thinking it must be something simple that I left out or did wrong, but for the life of me I can't find it!

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

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] = " & ctl.Column(4, i)
            
db.Execute strsql, dbFailOnError
ctl.Requery
Next i

CurrentDb.Execute strsql, dbFailOnError

End Sub

Open in new window

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.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Pretty sure the problem is that you cannot use the Column() property for a Combo or List box in a query (SQL).

mx
0
käµfm³d 👽Commented:
Do any of those columns contain string/text values? If so, then you need to single-quote the data that you are sending in your query.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
So, you would need to use a variable to get those values ... and then use that variable(s) in the SQL statement.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Mike EghtebasDatabase and Application DeveloperCommented:
Do this test to see what number your are getting:

MsgBox VarType(ctl.Column(0, i))

Most likely, you will get 8 (string). So you need to convert it to integer like
Clng(ctl.Column(0, i)) in this case
0
MeginAuthor Commented:
With the exception of the last piece of data, they are all numbers. The last part:

And [reportAs] = " & ctl.column(4, i)

is text. At least, the data in column 4 is text.

How do I write that with single-quotes?
0
MeginAuthor Commented:
The columns references are picking up the right data. When I hover over them with my curser, the correct numbers and text are showing.
0
Mike EghtebasDatabase and Application DeveloperCommented:
if SubTaskID is text, then try:

...  " AND [SubTaskID] = '" & ctl.Column(5, i) & "' ...

revised
0
MeginAuthor Commented:
eghtebas: tried it and it isn't working. I am getting the same error.

"Most likely, you will get 8 (string). So you need to convert it to integer like
 Clng(ctl.Column(0, i)) in this case" - Do I do this with each one of the columns that involves a number?

DatabaseMx:

"So, you would need to use a variable to get those values ... and then use that variable(s) in the SQL statement."  - Do you mean that I would have to dim a variable for each column and then set each one equal to the actual column (ctl.column(0,i)?  I am not sure about this and I have used this type of code in the past. The statement is picking up the data from the form. Are you sure that would be the problem?
0
MeginAuthor Commented:
I probably should have just written all of this out. The complete error reads as follows:


Syntax error in string in query expression '[TaskOrderID] = 783 AND [SubTaskID] = 156 AND [CompanyID] = 776 AND [PersonID] = 4080 AND [ReportAs] = Delete after test'.
0
Mike EghtebasDatabase and Application DeveloperCommented:
What did you get for:

MsgBox VarType(ctl.Column(0, i))
0
MeginAuthor Commented:
8
0
Mike EghtebasDatabase and Application DeveloperCommented:
In your table (SubTaskAssignment), do you have:

[TaskOrderID] Long
[SubTaskID] Long
[CompanyID] Long
 [PersonID] Long
 [ReportAs] Text

Sorry if you have mentioned before, just want to be sure. 8 was as expected. I will wait for your response for the above 5 items.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"The statement is picking up the data from the form. Are you sure that would be the problem?"

If you try to use Forms!SomeForm!SomeCombo.Column(3) in a query, it will fail to execute.

"Do you mean that I would have to dim a variable for each column and then set each one equal to the actual column (ctl.column(0,i)? "

Pretty much ... looks like five variables ...

However, it's possible the Execute SQL  handles this different that running from the query grid - and referencing the Column property.
0
Nick67Commented:
Your code has some messes in it

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
'ok great starting a loop

'Composing the SQL String to execute

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] = " & ctl.Column(4, i) 'here you may need quotes if this is a string value

'execute the deletions  
 db.Execute strsql, dbFailOnError      

'hey wait a second!  You're still in the loop.  Why are you requerying the listbox
ctl.Requery

'looping back to nail the next record
Next i

'you're done the loop.  Why do this again?
CurrentDb.Execute strsql, dbFailOnError

End Sub

If ctl.Column(4, i) is string data then

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)


Ought to get it done.
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Try making this change:


& " And [ReportAs] = " & Chr(34) &  ctl.Column(4, i) & Chr(34)

because that column is returning Text
0
Nick67Commented:
@mx
it's possible the Execute SQL  handles this different that running from the query grid

It'll go.
It's a listbox, and a multiselect one to boot, not a combobox.
She's composing a complete SQL string and then executing it.
As long as it's syntax correct, it'll work.
0
MeginAuthor Commented:
That totally worked! Adding the Chr(34) around the last item fixed it.

I also made the other recommendations you suggested, Nick67. Thank you so much!!!!!!
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.