Referring to Variable Value in Query Assembly
Posted on 2014-10-23
I've been using "Data Driven" query assembly for some time. This allows me to maintain the fields I want to include in a query in a table and then assemble the query by using fields in the table, like this:
strAppendSQL = "SELECT " & rsAppendFields!Source_Field
and then continue with this until the end of the recordset
strAppendSQL = strAppendSQL & rsAppendFields!Source_Field
The recordset rsAppendFields contains the fields that are being appended. Obviously, there is some intervening code to add enclosure characters like the apostrophe (for text) and the pound sign (for dates) if the field type requires it.
Now I want to have some of the fields in the rsAppendFields refer to VARIABLES that were populated earlier in the code. So for the particular record from rsAppendSource fields, the variable name might be "strValue". I want whatever is in the variable "strValue" to get added to the query. So if "strValue" had "Report1" in the variable, the first piece of the query I would want to read "SELECT 'Report1' As Expr1.
My problem is that when I refer to the 'strValue' variable as a field from the recordset, the query assembles like this:
SELECT 'strValue' As Expr1 instead of SELECT 'Report1' As Expr1. So the variable name - rather than its contents - is going into the query.
I tried using
strAppendSQL = strAppendSQL & rsAppendFields.Fields(rsAppendFields.Source_Field)
strAppendSQL = strAppendSQL & rsAppendFields.Fields(Source_Field)
The first throws "Item not found in this collection" error and the second just puts "strValue" in the query as before.
It works fine when I explicitly refer to the variable like this:
SELECT '" & strValue & "' AS Expr1,
But I would like to be able to use the recordset to populate the variables as well as the actual appended fields.
Any suggestions appreciated.
SELECT 'strValue' instead of the contents of the variable strValue.