Solved

Missing operator error in query on form controls

Posted on 2016-09-23
12
21 Views
Last Modified: 2016-10-09
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("qryRecipeNameAndDish")    

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.
0
Comment
Question by:Cindy Aitken
  • 5
  • 4
12 Comments
 
LVL 18
Comment Utility
try adding a space between IN and (

before you assign strSQL add this:
MsgBox strSQL
debug.print strSQL 

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.
0
 

Author Comment

by:Cindy Aitken
Comment Utility
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 ('','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','')));

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.
0
 
LVL 18

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
Comment Utility
it appears that none of the comboboxes you are looping through have any value except a zero-length string.  Do you SEE values?

instead of
 If Not IsNull(ctl.Value) Then
since your controls obviously are set to a zero-length string, test for:
 If len(trim(nz(ctl.Value,""))) >0 Then

instead of
Dim strSQL As String
do this:
Dim vSQL As variant

before the loop starts:
vSQL =null

in the loop:
vSQL  = (vSQL  + ",") & """" & ctl.Value & """"

when the loop is done:
if not isnull(vSQL) then
  'combine criteria with strBase
  strBase = strBase  & vSQL & ")"
   'change the SQL for the query, which must already exist -- I will leave this up to you ;)
 
end if

here is a general function I often include to change the SQL for a query. The query will be created if it does not yet exist:
'~~~~~~~~~~~~~~~~~~~~~~~~~~ MakeQuery
Function MakeQuery( _
   ByVal pQryName As String _
   , ByVal pSQL As String _
   , Optional pdb As DAO.Database _
   ) As Boolean
'crystal, strive4peace
'... 6-3-08, 130417, 160630
  
   'set up the error handler
   On Error GoTo Proc_Err
 
   'initialize the return value
   MakeQuery = False
   
   'dimension a database object variable
   Dim db As DAO.Database
   
   'initialize the db object
   If Not pdb Is Nothing Then
      'if a database object was passed, then use it
      Set db = pdb
   Else
      'otherwise, use the current database
      Set db = CurrentDb
   End If
   
'   uncomment next statement if desired
'   Debug.Print pQryName & " > " & pSql
 
   'use the database object
   With db
      'look up the query name in the MSysObjects table
      If Nz(DLookup("[Name]", "MSysObjects", _
               "[Name]='" & pQryName _
               & "' And [Type]=5"), "") = "" Then
         'if query does not exist, create the query
         .CreateQueryDef pQryName, pSQL
      Else
         'if query already exists, update the SQL
         'if query is open, close it
         On Error Resume Next
         DoCmd.Close acQuery, pQryName, acSaveNo
         On Error GoTo Proc_Err
         .QueryDefs(pQryName).SQL = pSQL
      End If
      'refresh the query collection
      .QueryDefs.Refresh
   End With 'db
   
   DoEvents
   
   'success
   MakeQuery = True
   
Proc_Exit:
   'exit code
   On Error Resume Next
   'release object variable
   Set db = Nothing
   Exit Function
   
Proc_Err:
   'error handler
   MsgBox Err.Description, , _
     "ERROR " & Err.Number & "  MakeQuery"
    
   Resume Proc_Exit
   Resume
End Function

Open in new window

for information on error handling:

1. basic error handling code for VBA (3:48)
http://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html
0
 

Author Comment

by:Cindy Aitken
Comment Utility
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("qryRecipeNameAndDish")    
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
***********************************************
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 18
Comment Utility
there are too many closing )

instead of Dim ctl As Access.Control
try
Dim ctl As Control
0
 

Accepted Solution

by:
Cindy Aitken earned 0 total points
Comment Utility
Crystal and Martin,

I managed to resolve the problem by changing the line:
    qdf.SQL = strBase & Left$(vSQL, Len(vSQL) - 2) & ")"
to
   qdf.SQL = strBase & vSQL & ")"

Guess Len was stripping off something important. :)

Thanks so much for your help!
0
 
LVL 18
Comment Utility
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
0
 

Author Comment

by:Cindy Aitken
Comment Utility
Thank you, Crystal!  That's good information to know. :)  I was wondering about the double quotes, but it makes sense now.
0
 
LVL 18
Comment Utility
the code for null propogation, which solved the issue, was given in #41813443
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
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…

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

12 Experts available now in Live!

Get 1:1 Help Now