Cindy Aitken
asked on
Missing operator error in query on form controls
I'm trying to write a query to return the selections on combo boxes in a form, which will run on the click of a command button, but I'm getting the "Missing operator" error. Here's my code. Can you spot the error?
Private Sub cmdGetMenuItems_Click()
Dim s As String
Dim strBase As String 'From SELECT to WHERE
Dim ctl As Access.Control
Dim strSQL As String 'the rest of the SELECT statement
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryRe
strBase = "SELECT [Recipe_Name] FROM tblRecipes WHERE [Recipe_Name] IN("
With Me
For Each ctl In .Controls
If ctl.ControlType = acComboBox Then
If Not IsNull(ctl.Value) Then
strSQL = strSQL & "'" & ctl.Value & "', "
' s = Left$(strSQL, Len(strSQL) - 2) & ")"
' MsgBox s
End If
End If
Next
End With
qdf.SQL = strBase & Left$(strSQL, Len(strSQL) - 2) & ")" 'Creates query
DoCmd.OpenQuery "qryRecipeNameAndDish", acViewNormal
Exit_cmdGetMenuItems_Click
Exit Sub
TIA for any help.
Private Sub cmdGetMenuItems_Click()
Dim s As String
Dim strBase As String 'From SELECT to WHERE
Dim ctl As Access.Control
Dim strSQL As String 'the rest of the SELECT statement
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryRe
strBase = "SELECT [Recipe_Name] FROM tblRecipes WHERE [Recipe_Name] IN("
With Me
For Each ctl In .Controls
If ctl.ControlType = acComboBox Then
If Not IsNull(ctl.Value) Then
strSQL = strSQL & "'" & ctl.Value & "', "
' s = Left$(strSQL, Len(strSQL) - 2) & ")"
' MsgBox s
End If
End If
Next
End With
qdf.SQL = strBase & Left$(strSQL, Len(strSQL) - 2) & ")" 'Creates query
DoCmd.OpenQuery "qryRecipeNameAndDish", acViewNormal
Exit_cmdGetMenuItems_Click
Exit Sub
TIA for any help.
ASKER
Thank you, Crystal.
I added the space between IN and (, but I'm still getting the error. Debug.Print and the MsgBox only show the results of the statement, not the statement for creating the query itself. When I look at the SQL for the query, I see:
SELECT tblRecipes.[Recipe_Name]
FROM tblRecipes
WHERE (((tblRecipes.[Recipe_Name
In Datasheet view, the query is empty, even though the FOR loop retrieves the correct selections from the combo boxes. They just aren't making it into the Select statement.
I've only been using Access and VBA for the past couple of weeks, so I'm not experienced enough to spot the error. That's why I'm asking for help. I'm pretty sure this is a simple syntax error, but I could stare at it for a month without seeing it.
I added the space between IN and (, but I'm still getting the error. Debug.Print and the MsgBox only show the results of the statement, not the statement for creating the query itself. When I look at the SQL for the query, I see:
SELECT tblRecipes.[Recipe_Name]
FROM tblRecipes
WHERE (((tblRecipes.[Recipe_Name
In Datasheet view, the query is empty, even though the FOR loop retrieves the correct selections from the combo boxes. They just aren't making it into the Select statement.
I've only been using Access and VBA for the past couple of weeks, so I'm not experienced enough to spot the error. That's why I'm asking for help. I'm pretty sure this is a simple syntax error, but I could stare at it for a month without seeing it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Crystal,
That's just it -- the combo boxes DO have values. I inserted a Debug.Print statement in the loop to make sure the values were being picked up. Here's the Debug.Print output reflecting the actual values in the combo boxes:
"Bacon and Eggs")
"Bacon and Eggs","Avocado BLT")
"Bacon and Eggs","Avocado BLT","Yogurt")
"Bacon and Eggs","Avocado BLT","Yogurt","Shepherd's Pie")
"Bacon and Eggs","Avocado BLT","Yogurt","Shepherd's Pie","Waldorf salad")
"Bacon and Eggs","Avocado BLT","Yogurt","Shepherd's Pie","Waldorf salad","Wild Rice with Veggies")
"Bacon and Eggs","Avocado BLT","Yogurt","Shepherd's Pie","Waldorf salad","Wild Rice with Veggies","Corn Salsa")
It appears to be falling apart when the values collected by the loop are added to the end of the Select statement. I followed your instructions, and the query's SQL statement now ends after the From clause. Somehow, the Where clause and all those quotes and commas have been stripped off:
SELECT tblRecipes.[Recipe_Name]
FROM tblRecipes;
The revised code is below. Thank you for taking the time to look it over and help me track down the mistake.
**************************
Private Sub cmdGetMenuItems_Click()
Dim s As String 'Used with Debug.Print
Dim strBase As String 'Select clauses
Dim ctl As Access.Control
Dim vSQL As Variant
'Dim strSQL As String 'Previously held the output of the loop
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryRe
strBase = "SELECT [Recipe_Name] FROM tblRecipes WHERE [Recipe_Name] IN ("
vSQL = Null
With Me
For Each ctl In .Controls
If ctl.ControlType = acComboBox Then
If Not IsNull(ctl.Value) Then
vSQL = (vSQL + ",") & """" & ctl.Value & """"
s = Left$(vSQL, Len(vSQL)) & ")"
Debug.Print s
End If
End If
Next
End With
If Not IsNull(vSQL) Then
qdf.SQL = strBase & Left$(vSQL, Len(vSQL) - 2) & ")"
End If
**************************
That's just it -- the combo boxes DO have values. I inserted a Debug.Print statement in the loop to make sure the values were being picked up. Here's the Debug.Print output reflecting the actual values in the combo boxes:
"Bacon and Eggs")
"Bacon and Eggs","Avocado BLT")
"Bacon and Eggs","Avocado BLT","Yogurt")
"Bacon and Eggs","Avocado BLT","Yogurt","Shepherd's Pie")
"Bacon and Eggs","Avocado BLT","Yogurt","Shepherd's Pie","Waldorf salad")
"Bacon and Eggs","Avocado BLT","Yogurt","Shepherd's Pie","Waldorf salad","Wild Rice with Veggies")
"Bacon and Eggs","Avocado BLT","Yogurt","Shepherd's Pie","Waldorf salad","Wild Rice with Veggies","Corn Salsa")
It appears to be falling apart when the values collected by the loop are added to the end of the Select statement. I followed your instructions, and the query's SQL statement now ends after the From clause. Somehow, the Where clause and all those quotes and commas have been stripped off:
SELECT tblRecipes.[Recipe_Name]
FROM tblRecipes;
The revised code is below. Thank you for taking the time to look it over and help me track down the mistake.
**************************
Private Sub cmdGetMenuItems_Click()
Dim s As String 'Used with Debug.Print
Dim strBase As String 'Select clauses
Dim ctl As Access.Control
Dim vSQL As Variant
'Dim strSQL As String 'Previously held the output of the loop
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryRe
strBase = "SELECT [Recipe_Name] FROM tblRecipes WHERE [Recipe_Name] IN ("
vSQL = Null
With Me
For Each ctl In .Controls
If ctl.ControlType = acComboBox Then
If Not IsNull(ctl.Value) Then
vSQL = (vSQL + ",") & """" & ctl.Value & """"
s = Left$(vSQL, Len(vSQL)) & ")"
Debug.Print s
End If
End If
Next
End With
If Not IsNull(vSQL) Then
qdf.SQL = strBase & Left$(vSQL, Len(vSQL) - 2) & ")"
End If
**************************
Could there be something in one of the names that causes Access to think that it's the end of the statement? What is the last item that shows up in the query string?
there are too many closing )
instead of Dim ctl As Access.Control
try
Dim ctl As Control
instead of Dim ctl As Access.Control
try
Dim ctl As Control
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you're welcome ~ happy to help
Glad you got it!
the beauty of null propagation (using a variant to construct the Where parts) is that you don't have to worry about adjusting the where clause afterward.
if the variable is initialized to Null, then in the loop, this:
vSQL = (vSQL + ",") & """" & ctl.Value & """"
means to only add a comma if vSQL is not null.
+ will make everything in the parentheses Null if anything is Null.
& concatenates Nulls.
I also used double quote instead of single quote in case one of the values may have a single quote. Inside a quoted string with double quotes, use 2 of them to get 1 of them
Glad you got it!
the beauty of null propagation (using a variant to construct the Where parts) is that you don't have to worry about adjusting the where clause afterward.
if the variable is initialized to Null, then in the loop, this:
vSQL = (vSQL + ",") & """" & ctl.Value & """"
means to only add a comma if vSQL is not null.
+ will make everything in the parentheses Null if anything is Null.
& concatenates Nulls.
I also used double quote instead of single quote in case one of the values may have a single quote. Inside a quoted string with double quotes, use 2 of them to get 1 of them
ASKER
Thank you, Crystal! That's good information to know. :) I was wondering about the double quotes, but it makes sense now.
the code for null propogation, which solved the issue, was given in #41813443
before you assign strSQL add this:
Open in new window
the message box shows you what it is and then if you need to copy it, you can press Ctrl-G in the Immediate (debug) window and make a query with it. I believe if you look, however, you will see what is wrong. If you do not, paste in the SQL and let us look at it.