Inserting Variable into SQL

[tstansberry]

Sorry for the rather elementary question but I desperately need to
insert a variable into a SQL Select query that I am executing in some
VBA code for an access DB.

The query is this:

Select Sample_Table.*
From Sample_Table
Where Sample_Table.Distributor = VARIABLE1 and Sample_Table.Country =
VARIABLE2;

I also was having trouble getting a select query to go. I have
learned that doCmd.RunSQL will not work for select queries, but I
don't know what to use.

Any help is greatly appreciated.

Thanks,

Taylor

 

[Albert D. Kallal]

As always to give a really good answer this question, it's always in the
details!

you're saying you want to insert variables into some SQL, but you get no
mention of what you actually want to do with that SQL, and therein lies the
problem.

for any serious application development, the results of green really have to
go somewhere, perhaps are using a report, or perhaps to what the results to
go to go to a form?

I mean there must be *something* you want to do with this data.....

This is also why in code you can't just simply execute some SQL select,
throw it up in the air, and hope that the resulting output of sql lands
somewhere and access knows what to do with it....

You have to tell me the "where" and the "what" you want to do with the
sql....

In fact, what this really means is that for most cases you don't actually
have to modify the actual SQL, the forms and reports have a built in feature
to deal with exactly the problems that you're asking to solve -- thus you
can use the where clause to make or restrict or "add" criteria to your SQL
without actually having to modify the actual SQL its self.

' select what City for the report
strWhere = "City = '" & cboCity & "'"

docmd.OpenReport "mYReprt",acViewPreview,,strWhere

In the above, you can see were using a combo box on a form, but we could
have simply used a variable in place of the cboCity.

So, in *most* cases you new simply use the where clause ......

However, if your example code was a udpate query...then you can go:

eg:
dim strSql as string
DIM VARIABLE1 as string
DIM VARIABLE2 as string

VARIABLE1 = "United States"
VARIABLE2 = "USA"

strSql = update Sample_Table set Country = " & VARIABLE2 & _
" WHERE Distributor = 'ABC' and " & _
" Country = '" & VARIABLE2

docmd.runSQL strSql

Actually, most of the time we don't want confirmation prompts..so use:

currentdb.Execute strSql

 

[Yanick]

First of all, you will need to assign your SQL statement to a string variable.

MySQLVariable = "Select Sample_Table.* From Sample_Table Where
Sample_Table.Distributor = '" & VARIABLE1 & "' and Sample_Table.Country = '"
& VARIABLE2 & "'";

For string variable you need to use ' before and after the variable.
For number, put nothing and for date use #.

Then use this to change your query SQL :
Dim CurrentDB As DAO.Database

Set CurrentDB = DBEngine(0)(0)
CurrentDB.QueryDefs("NameOfYourQuery").SQL = MySQLVariable

docmd.OpenQuery "NameOfYourQuery"


Depending of what your triying to do, the are much easier ways to access
data trought VBA. I will need more detail if you need a other solution.

 

[Pat Hartman]

RunSQL is intended to run action queries. If you want to open a query for
your users to view (not a good idea), use OpenQuery. If you want to open a
recordset for processing with vba, use .OpenRecordset.

You cannot use VBA variables in SQL because they are totally different
environments. If you want to pass a value to your query, you have two
methods
1. refer to a form field -
Where Sample_Table.Distributor = Forms!yourform!yourcontrolname
2. use a function -
Where Sample_Table.Distributor = MyFunction()

SQL when running within Access can "see" form fields and user defined
functions as well as VBA functions. If you build the SQL string in code,
you can concatenate variables or whatever you want.

strSQL = "Select Sample_Table.* From Sample_Table Where
Sample_Table.Distributor = " & Me.NumericVariable & " AND
Sample_Table.Country = '" & Me.TextVariable & "';"

 

[tstansberry]

The results of this query I want to export to an excel file uniquely
named VARIABLE2_VARIABLE1.xls. All of this will be in a loop where I
have another query collecting the list of unique
distributors(VARIABLE1) and countries(VARIABLE2) that I cycle
through. In the end It should create 150 unique excel files with each
file containing the Individual Distributor's data for the given
country.

From a high level look here is what I am trying to accomplish. I have
one table that contains all of the individual contracts for all of the
distributors world wide. I have one query that gets me the list of
unique distributors and countries. The four listed below represent
four unique excel files.
ex:
acme inc, USA
acme inc, Canada
Widgets inc, USA
Widgets inc, Canada

Next I have a query that gathers all of the necessary data for these
excel files. The example that I posted initially was trimmed down to
save space. Below is the actual query:

SELECT SMS3_SAMPLE_DATA.Distributor, SMS3_SAMPLE_DATA.ListPrice,
SMS3_SAMPLE_DATA.[Net Price], Date_Adj_END.Clean_End_Date,
Date_Adj_START.Clean_Start_Date, SMS3_SAMPLE_DATA.[Quote Type],
SMS3_SAMPLE_DATA.[Contract#], SMS3_SAMPLE_DATA.[Service Level],
SMS3_SAMPLE_DATA.[Item Name], SMS3_SAMPLE_DATA.[Serial Number],
SMS3_SAMPLE_DATA.[Created By], SMS3_SAMPLE_DATA.[Ordered By],
Data_Adj_CONVERSION.Clean_Conversion_Date, SMS3_SAMPLE_DATA.[Quote
Num], SMS3_SAMPLE_DATA.[Disti PO#], SMS3_SAMPLE_DATA.[Disti Billto
Country], SMS3_SAMPLE_DATA.RES_ST1, SMS3_SAMPLE_DATA.RES_ST2,
SMS3_SAMPLE_DATA.RES_ST3, SMS3_SAMPLE_DATA.RES_ST4,
SMS3_SAMPLE_DATA.RES_CITY, SMS3_SAMPLE_DATA.RES_STATE,
SMS3_SAMPLE_DATA.RES_ZIP_CODE, SMS3_SAMPLE_DATA.RES_COUNTRY,
SMS3_SAMPLE_DATA.ORDER_NUM, SMS3_SAMPLE_DATA.STS_CODE,
SMS3_SAMPLE_DATA.Reseller, SMS3_SAMPLE_DATA.[Reseller PO #],
SMS3_SAMPLE_DATA.[RESELLER CONTACT FIRST NAME], SMS3_SAMPLE_DATA.
[RESELLER CONTACT LAST NAME], SMS3_SAMPLE_DATA.[RESELLER CONTACT
PHONE], SMS3_SAMPLE_DATA.[RESELLER CONTACT EMAIL], SMS3_SAMPLE_DATA.
[End Customer], SMS3_SAMPLE_DATA.[EU Contact FIRST NAME],
SMS3_SAMPLE_DATA.[EU Contact LAST NAME], SMS3_SAMPLE_DATA.[EU Contact
PHONE], SMS3_SAMPLE_DATA.[EU Contact EMAIL], SMS3_SAMPLE_DATA.[Address
1], SMS3_SAMPLE_DATA.[Address 2], SMS3_SAMPLE_DATA.[Address 3],
SMS3_SAMPLE_DATA.[Address 4], SMS3_SAMPLE_DATA.City, SMS3_SAMPLE_DATA.
[Postal Code], SMS3_SAMPLE_DATA.Country
FROM SMS3_SAMPLE_DATA, Data_Adj_CONVERSION, Date_Adj_END,
Date_Adj_START
WHERE SMS3_SAMPLE_DATA.Distributor=[VARIABLE1] And
SMS3_SAMPLE_DATA.RES_COUNTRY=[VARIABLE2];

This query needs to be executed for every record in the first query,
and the results of the query need to exported to excel files. For the
four above referenced distributors it should created the following
four files.
acme inc_USA.xls
acme inc_Canada.xls
Widgets inc_USA.xls
Widgets inc_Canada.xls

That is the entire project.
Thanks again for any help you can provide,
Taylor

 

[Albert D. Kallal]

All of this will be in a loop where I
have another query collecting the list of unique
distributors(VARIABLE1) and countries(VARIABLE2) that I cycle
through.

You likey don't need 2 quries....

acme inc, USA
acme inc, Canada
Widgets inc, USA
Widgets inc, Canada

Next I have a query that gathers all of the necessary data for these
excel files. The example that I posted initially was trimmed down to
save space. Below is the actual query:

Join this query to the above 1st query that builds the lists of
distributoers...

This query needs to be executed for every record in the first query,

Acutally, just join this query to the 1st...you get the data, and you not
have to execute a query for each row from the 1st table.

and the results of the query need to exported to excel files. For the
four above referenced distributors it should created the following
four files.

I would have the 2 queris joined, and then setup two funciotns for the
conditions:

in a standard code module, we palce:


public gblDist as string
public gblCountry as string

Public Function MyDist() as string

MyDist = bglDist

end if

Public Funciton MyCountry() as string

MyCountry = gblCountry

end if

Now, add to your "joined" query, the follwing coditions

where Country = MyCountry() and Distriboer = MyDist()

Now, to export each file, go:

dim rstDist as dao.RecordSet
dim strSql as string
dim strOutPutFile as string

strSql = "select my country and distribooer list sql goes here"

do while rstDist.Eof = false
gblCountry = rstDist!Country
gblDist = rstDist!Distriuboer

strOutPutFile = "c:\outdata\" & gblDist & "_" & gblCounry & ".xls"

docmd.TransferSpreadsheet
acExport,acSpreadsheetTypeExcel9,"qryIJoined",strOUtfile,true\

rstDist.MoveNext
loop
rstdist.close

The above is air code..but, it about 99% close to what will work. The trick
is two things:

1) join the 1st table with country + dist to the 2nd table with the data

2) the above global variables can be used as a filter on this joined query

3) simply use transferspreadsheet on that joined query, and we get a double
bonus of making the filter *and* setting the correct output file name based
on those two values...

 

[Taylor_Made]

Thanks for all of your help, but I have one last question. I keep
getting this error "Object variable or with block variable not set",
but it seems to be set. I am not sure what I am doing wrong. below
is the entire set of code I have created for this issue. The error is
related to the three lines starting with "Do While rstDist.EOF =
False". It seems that the two variables below are not set, but
weren't they set above? I'm sorry I am a novice. Thanks again for
all of your help!

Option Compare Database
Public gblDist As String
Public gblCountry As String

Public Function MyDist() As String

MyDist = gblDist

End Function

Public Function MyCountry() As String

MyCountry = gblCountry

End Function

Public Function OutPutToExcel()

Dim rstDist As DAO.Recordset
Dim strSql As String
Dim strOutPutFile As String

strSql = "SELECT MAIN_DATA_FORMAT.* FROM Disti_List LEFT JOIN
MAIN_DATA_FORMAT ON
(Disti_List.Distributor=MAIN_DATA_FORMAT.Distributor) AND
(Disti_List.RES_COUNTRY=MAIN_DATA_FORMAT.RES_COUNTRY) Where
RES_Country = MyCountry() and Distributor = MyDist()"


Do While rstDist.EOF = False
gblCountry = rstDist!country
gblDist = rstDist!Distributor

strOutPutFile = "C:\Documents and Settings\tstansbe\My Documents\2
Tier Renewals Report\test_file_output\" & gblDist & "_" & gblCounry &
".xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryIJoined", strOUtfile, True

rstDist.MoveNext
Loop
rstDist.Close

End Function

 

[Albert D. Kallal]

Option Compare Database

option Explicit <----- ALWAYS ALWAYS ALWAYS
add this to your code...

Do While rstDist.EOF = False

rstDist???? Where did you define this variable?????

(you have to define all varabiles..

eg:

dim rstDist as dao.RecordSet

strSql = "SELECT MAIN_DATA_FORMAT.* FROM Disti_List LEFT JOIN
MAIN_DATA_FORMAT ON
(Disti_List.Distributor=MAIN_DATA_FORMAT.Distributor) AND
(Disti_List.RES_COUNTRY=MAIN_DATA_FORMAT.RES_COUNTRY) Where
RES_Country = MyCountry() and Distributor = MyDist()"

Do you really need a join in the above? Furthermore, the above is WHERE WE
ARE TO GET the "list" OF LEGAL countries and distributor names. This is NOT
our query we going to use for the final output (data export).

So ALL WE want here is a SIMPLE LIST of dist and country names to "process".
I don't rally see the need for the "join" here....do you?? The above
"conditions" = MyDist() etc. is to be placed in the ACTUAL query we going to
use for export.

So, our the pseudo code is:


1) Build a simple list using SQL of our countries and distributors which we
want to export for.

2) for each iteration of the above loop we will set the distributor +
country, and then execute a transfer spreadsheet.

So, we likely will build this list of distributor + countries in SQL. This
SQL will not have any conditions in it, and most likey will not be joined to
other tables. Also, as a note in place of pasting that big messy junk of SQL
into your code, simply use the query builder and execute code to grab the
data from the query builder

eg:

set rst = currentdb.QueryDefs("name of query").Execute

The above means you don't have to have all that messy sql in your code.

If you don't plan to use a query as above, then in your example code you
left out the loading of the reocrdset. eg, you must go:

set rstDist = currentdb.OpenrecordSet(strSql)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryIJoined", strOUtfile, True

The above looks ok. Note in the above, it is assumed you built a query
called "qryIJoined". It is this query that will have the above conditions
that were feeding via the looping code. For each iteration of the loop, we
will send(set) the distributor, and country, and then execute a
TransferSpreadsheet that has the sql based on these conditions. This goes
back to your original question as to how you put variables in the SQL
example. We are placing (setting) the distributor + country for each loop,
and the transferSpreadsheet will thus use this "new" sql with the
conditions.

I assume that you built a query called "qryIJoined" in the query builder?
(and, it has the two condstions it it????).

also keep mind that the two functions we make must go in a standard code
module, and cannot be placed in a form's module
eg:

Option Compare Database
Public gblDist As String
Public gblCountry As String

Public Function MyDist() As String

MyDist = gblDist

End Function

Public Function MyCountry() As String

MyCountry = gblCountry

End Function

the other code you have can be placed behind a button on a form.....but, the
above funcitons are GLOBAL and must be placed in a standard code module
before the SQL will see these values.