Solved

Referring to Variable Value in Query Assembly

Posted on 2014-10-23
5
105 Views
Last Modified: 2015-03-22
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)

and

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.
0
Comment
Question by:Buck_Beasom
  • 3
  • 2
5 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 40400333
how do you set the value for variable "strValue" ?

this should work

Dim strValue As String, sql As String
strValue = "Report1"

sql = "Select '" & strValue & "' As Expr1 From TableX"

Debug.Print sql
0
 

Author Comment

by:Buck_Beasom
ID: 40400350
This is the same as my "explicit" code and it works. But when "strValue" is pulled from a table into a recordset and I refer to the value from the recordset, it puts "strValue" into the query instead of the CONTENTS of the variable strValue. So I need something to tell the code that what I want in the query is the CONTENTS of the variable strValue rather than just the string "strValue."

Thanks.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40400368
<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?
0
 

Author Comment

by:Buck_Beasom
ID: 40400471
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_Field

Encounters a VARIABLE in rsAppendFields!Source_Field instead of a field name in the source table. What I need is a way to tell it that if rsAppendFields!Source_Field 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_Field to insert the CONTENTS of strValue ("Report1") it puts "strValue" in instead of "Report1."

Thanks for your assistance.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40400493
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"?
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

947 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

23 Experts available now in Live!

Get 1:1 Help Now