Solved

Error: Syntax Error (Missing operator) in query expression

Posted on 2014-12-29
17
251 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
  • 6
  • 4
  • 4
  • +2
17 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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 74

Expert Comment

by:käµfm³d 👽
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
So, you would need to use a variable to get those values ... and then use that variable(s) in the SQL statement.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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
Comment Utility
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
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
if SubTaskID is text, then try:

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

revised
0
 

Author Comment

by:Megin
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Megin
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
What did you get for:

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

Author Comment

by:Megin
Comment Utility
8
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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
Comment Utility
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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
Comment Utility
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
Comment Utility
@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
Comment Utility
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now