Solved

Error: Syntax Error (Missing operator) in query expression

Posted on 2014-12-29
17
259 Views
Last Modified: 2014-12-29
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

0
Comment
Question by:Megin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
  • +2
17 Comments
 
LVL 75
ID: 40522803
Pretty sure the problem is that you cannot use the Column() property for a Combo or List box in a query (SQL).

mx
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 40522804
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
 
LVL 75
ID: 40522805
So, you would need to use a variable to get those values ... and then use that variable(s) in the SQL statement.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40522806
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
 

Author Comment

by:Megin
ID: 40522810
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
 

Author Comment

by:Megin
ID: 40522812
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
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40522814
if SubTaskID is text, then try:

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

revised
0
 

Author Comment

by:Megin
ID: 40522823
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
 

Author Comment

by:Megin
ID: 40522833
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
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40522836
What did you get for:

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

Author Comment

by:Megin
ID: 40522838
8
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40522841
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
 
LVL 75
ID: 40522843
"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
 
LVL 26

Accepted Solution

by:
Nick67 earned 250 total points
ID: 40522846
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 250 total points
ID: 40522847
Try making this change:


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

because that column is returning Text
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40522848
@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
 

Author Closing Comment

by:Megin
ID: 40522853
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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question