Passing an entire array to a query

[clotposlo3]

ADezii,

I really can't thank you enough for your help!

After adapting your code to my database, unfortunately, I'm still missing an operator.

The error message says:

Syntax error (missing operator) in '[Recipe_Name] IN('Eggs Benedict','blah blah')'. Just like that -- no space between "IN" and the open parenthesis, and a single quote before the period at the end of the statement.

Shouldn't the error message start with Select? Is it even seeing anything up to the Where clause? It's picking up the values of the combo boxes, but when I look at the query in SQL view, it's still just a bunch of quotes and commas where the recipe names should be.

The only difference between your code (that you guaranteed to work) and mine is the loop that picks up the values in the combo boxes instead of using the array. (See below.) I copied and pasted your strBase and qdf.SQL statements into my code without making any changes.

With Me
  For Each ctl In .Controls
    If ctl.ControlType = acComboBox Then
        If Not IsNull(ctl.Value) Then
            strSQL = strSQL & "'" & ctl.Value & "', "
        End If
    End If
  Next
End With

[ADezii]

At tis point, the only Option that I see is for you to Upload the Database, or a subset of it, so that we can see it firsthand. Sometimes you actually need to be looking at the DB in front of you, and this appears to be one of those cases.

[PhilOfWalton]

You really do seem to be struggling

Again a very rough Db but you add what recipes you want for each day's meals and it creates the list

Phil

[clotposlo3]

PhilofWalton and ADezii,

The issue is FINALLY resolved! I knew the data in the combo boxes was making it through the loop and that there was something wrong with the way the two parts of the SELECT statement were being put together. So, on a whim, I changed the line:

qdf.SQL = strBase & Left$(strSQL, Len(strSQL) - 2) & ")"
to
qdf.SQL = strBase & strSQL & ")"

and BAM! It works beautifully! Guess LEN was stripping off something important. :)

Thank you both so very much for your help!!!!

[ADezii]

Happy that it works for you, but it really shouldn't since you are leaving "', " at the end of strSQL where it needs to be trimmed

Left$(strStrSQL, Len(strSQL)-3)

prior to appending the closing ")". Well, I guess you can't argue with success! (LOL).

[clotposlo3]

You are right, there are other changes. I also changed strSQL to type Variant and set it to Null before the loop. Then the meat of the loop goes like this:

vSQL = (vSQL + ",") & """" & ctl.Value & """"

After the loop finishes, I also changed the line that connects the Select statement to the output from the loop, thus:

If Not IsNull(vSQL) Then
    qdf.SQL = strBase & vSQL & ")"
End If

...except I still had the LEN stuff in there. And it was still blowing up. I knew the problem was not in the loop, but in the qdf.SQL line. I really didn't know what to do, but I had to figure out what was wrong with that line, so I started deleting parts of it just to see what happened. The first thing I deleted was the Left$(strSQL, Len(strSQL) - 2) part, and lo and behold, it decided to work. I'm not sure why it worked, but as you say, you can't argue with success!

I have a lot of work left to do on this database before I can really use it. I'm sure I'll blow it up a few more times and will be back asking for help.

Many thanks to all who contributed their time and expertise!