Solved

Error: Syntax Error (Missing operator) in query expression

Posted on 2014-12-29
17
256 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
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 33

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 33

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 33

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 33

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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

805 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