Buck Beasom
asked on
Referring to Variable Value in Query Assembly
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_Fiel d
and then continue with this until the end of the recordset
strAppendSQL = strAppendSQL & rsAppendFields!Source_Fiel d
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(rsAp pendFields .Source_Fi eld)
and
strAppendSQL = strAppendSQL & rsAppendFields.Fields(Sour ce_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.
strAppendSQL = "SELECT " & rsAppendFields!Source_Fiel
and then continue with this until the end of the recordset
strAppendSQL = strAppendSQL & rsAppendFields!Source_Fiel
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(rsAp
and
strAppendSQL = strAppendSQL & rsAppendFields.Fields(Sour
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<But when "strValue" is pulled from a table into a recordset and I refer to the value from the recordset >
how are you doing this?
how are you doing this?
ASKER
I have a table that contains the Import Routine number, the row order, the Source_Field, The Destination_Field, the Field Type, a variable so I can use conversion functions like "Cdbl(Value) As Expr1" in the import, and a field identifying the last row.
The query is assembled in a loop like this:
strAppendSQL = "INSERT INTO [" & strDestinationTable & "] ( "
Do Until rsAppendFields.EOF
strAppendSQL = strAppendSQL & "[" & rsAppendFields!Dest_Field & "] "
If rsAppendFields!LastField = True Then
strAppendSQL = strAppendSQL & " )"
Else
strAppendSQL = strAppendSQL & ", "
End If
rsAppendFields.MoveNext
Loop 'rsAppendFields - Insert Section
This is the first section the specifies the insert fields (destination.) The second part is longer because it has all the code for handling enclosure characters based on field type: Apostrophes for strings, # for dates and so forth. It works fine for everything EXCEPT when this line of code:
strAppendSQL = strAppendSQL & rsAppendFields!Source_Fiel d
Encounters a VARIABLE in rsAppendFields!Source_Fiel d instead of a field name in the source table. What I need is a way to tell it that if rsAppendFields!Source_Fiel d is a VARIABLE, I want the CONTENTS of the variable and not the NAME of the variable in the SQL. Coding it explicitly - as you suggested above - works, but when I try to use rsAppendFields!Source_Fiel d to insert the CONTENTS of strValue ("Report1") it puts "strValue" in instead of "Report1."
Thanks for your assistance.
The query is assembled in a loop like this:
strAppendSQL = "INSERT INTO [" & strDestinationTable & "] ( "
Do Until rsAppendFields.EOF
strAppendSQL = strAppendSQL & "[" & rsAppendFields!Dest_Field & "] "
If rsAppendFields!LastField = True Then
strAppendSQL = strAppendSQL & " )"
Else
strAppendSQL = strAppendSQL & ", "
End If
rsAppendFields.MoveNext
Loop 'rsAppendFields - Insert Section
This is the first section the specifies the insert fields (destination.) The second part is longer because it has all the code for handling enclosure characters based on field type: Apostrophes for strings, # for dates and so forth. It works fine for everything EXCEPT when this line of code:
strAppendSQL = strAppendSQL & rsAppendFields!Source_Fiel
Encounters a VARIABLE in rsAppendFields!Source_Fiel
Thanks for your assistance.
you posted a lot of words without answering the question..
where is the code that opens the recordset "rsAppendFields" ?
where is the code that sets the value for the variable "strValue"?
where is the code that opens the recordset "rsAppendFields" ?
where is the code that sets the value for the variable "strValue"?
ASKER
Thanks.