Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Error: Syntax Error (Missing operator) in query expression

Posted on 2014-12-29
17
Medium Priority
?
268 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
Industry Leaders: 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 1000 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 1000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

636 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