Using string variable in DoCmd.RunSQL

[dcdimon]

Okay, I'm seeing single and double quotes swimming through the air and I still can't figure out the syntax for what I'm trying to do. Before I start talking to the wee people in my head I thought I'd bring my question here.



I'm trying to construct a string to use with the DoCmd.Runsql vba command that will allow me to pass a variable to it as I loop through a list of prior query names and append the results to a table. I have a string already constructed that explicitly lists the query to use. The problem I'm having is constructing the string to see and use the variable. My original string is below:

dim SQLstr as string

SQLstr = "INSERT INTO FieldListTable ( FieldPickDate, FieldNumber, fieldname, ControlName, OORField, ControlTag ) " & _
"SELECT FieldPickDate, DCount(""id"",""UFO2_IsolatorFieldPollQ"",""id <= "" & [id]) AS FieldNumber, ControlSource, " & _
"ControlLabel, ControlOOR, ControlTag FROM UFO2_IsolatorFieldPollQ"

DoCmd.RunSQL SQLstr

This works fine and does exactly what I'm looking for with the UFO2_IsolatorFieldPollQ query. What I now want to do is use a variable to insert other text (stored in a table) where the Isolator text is; such as...

UFO2_variablestringFieldPollQ

I'm trying to build this append string so that it can dynamically change the queries it runs off of as query names change or or added to a list (table).

I'm using another string variable to hold the new text and, no matter how I include it, I can't get it to see the variable or it sees the variable but my DLookup function isn't seen as a function. I'm sure it's got something to do with my use (or lack of) quotes and the combination of double and single quotes. I'm looking for a string in the form of what I have below (I realize the quotes around the variable aren't correct).

dim SQLstr as string
dim Areastr as string

Areastr = dlookup (another table)

SQLstr = "INSERT INTO FieldListTable ( FieldPickDate, FieldNumber, fieldname, ControlName, OORField, ControlTag ) " & _
"SELECT FieldPickDate, DCount(""id"",""UFO2_" & Areastr & "FieldPollQ"",""id <= "" & [id]) AS FieldNumber, ControlSource, " & _
"ControlLabel, ControlOOR, ControlTag FROM UFO2_" & AreaStr & "FieldPollQ"

DoCmd.RunSQL SQLstr

Any ideas? Or am I totally incorrect in thinking I can create a string for this operation without specifically naming the main query?

[June7]

Building the table/query name dynamically in VBA is fine. Embedding a domain aggregate in SQL constructed with VBA is always tricky.

Is ID a number type field?

Try:

,""id <= "" & [id] & """")

[dcdimon]

The ID field is a number data type.

Everything works as expected in my unaltered string. The problem comes when I'm trying to insert a variable to complete the query name that the script gets its data from.

Would inserting a variable change how the ID portion is working? Or is it changing the syntax for quotes?

I'll give this a try and let you know how it worked.

[dcdimon]

I've got it working - I seemed to recall getting an error message at one point on the day of my post regarding mismatched types. At the time I had been banging my head against my keyboard for so long it didn't really sink in what that was telling me. I was getting it to work, but the data type it was trying to write didn't match the field data type.

So I went back and looked at my RunSQL string and decided to rework the variable I was passing. I made the whole query name portion the variable and then enclosed that in the correct syntax of single and double quotes and voila it worked! I startled my office mate when I yelled in triumph.

Here’s my final RunSQL script. I have the RunSQL string inside a loop that references a table that contains the different areas that need to have a query run for them. Basically that table contains the variable in the name of the queries. I've used three double quotes (colored red for visibility) on either side of my query name string (Areastr) in order to get the string to parse correctly.

Set rs = DBEngine(0)(0).OpenRecordset("UFO2_FillerCLAreasT")Do While Not rs.EOF
Areastr = "UFO2_" & rs!InspectionArea & "FieldPollQ"


SQLstr = "INSERT INTO [UFO2_FillerFieldListT] ( FieldPickDate, FieldNumber, fieldname, ControlName, OORField, ControlTag ) " & _
"SELECT FieldPickDate, DCount(""id"", """ & Areastr & """,""id <= "" & [id]) AS FieldNumber, ControlSource, " & _
"ControlLabel, ControlOOR, ControlTag FROM " & Areastr

Debug.Print SQLstr

DoCmd.RunSQL SQLstr


rs.MoveNext
Loop
rs.Close

I also used the Debug.Print command to view how the SQLstr was getting passed to the Docmd.RunSQL command. I found that there were some extraneous quotes in the string that were giving the command fits. Seeing what the command was trying to execute was extremely helpful and took away the guess work of what was going on.

Hopefully this can help someone else down the road if they meet the same roadblock.

DD

[June7]

Glad you have it working. In my testing I did not need to do that, my suggested edit worked for me. At least it did not error. I should take another look an make sure the data returned was correct.