Pass parameter to query using vba

[armesca]

I am attempting to reference a table and create 10 different queries based on the parameter- Region #(1-10). This is my attempt at getting my first query filtered by Region I. I am trying to write this in VBA, and am getting an error: Compile Error: Syntax error. Here is what I have so far:

Public Sub param_q_select()

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim sqry As String
Set db = CurrentDb
sqry = "Select * FROM Parameter_Test WHERE Parameter_Test.Region ="Region I")
Set qd = db.CreateQueryDef("DLSGP_RegionI", sqry)
End Sub



Any help is appreciated!

 

[prley]

I am attempting to reference a table and create 10 different queries based on the parameter- Region #(1-10). This is my attempt at getting my first query filtered by Region I. I am trying to write this in VBA, and am getting an error: Compile Error: Syntax error. Here is what I have so far:

Public Sub param_q_select()

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim sqry As String
Set db = CurrentDb
sqry = "Select * FROM Parameter_Test WHERE Parameter_Test.Region ="Region I")
Set qd = db.CreateQueryDef("DLSGP_RegionI", sqry)
End Sub



Any help is appreciated!

How bout.

Public Sub param_q_select()

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim sqry As String
Dim iCnt as integer 'counter
dim stRegion as String
dim stQuery as string

Set db = CurrentDb
for iCnt = 1 to 10
stRegion = "Region " & iCnt
stQuery = "DLSGP_Region" & iCnt
sqry = "Select * FROM Parameter_Test WHERE Parameter_Test.Region =& stRegion &
Set qd = db.CreateQueryDef(stQuery, sqry)
next iCnt
End Sub

The syntax might not be 100% but you get the idea
cheers!

 

[boblarson]

First up - Why create 10 queries? Why not use one reusable one?

Second, the original syntax is fine except to remove the paren ) at the end of the line.

 

[armesca]

I don't need 10 queries, but I do need 10 different, separate results,(one for each region), be it a table or something else as the end result

 

[boblarson]

When you say "separate results" what are you envisioning? Excel Files? Reports to print or save for each?

I am asking because what the output you desire can influence how we get there.

 

[armesca]

I have been using separate queries to output data to Excel (see code below for example), and this works fine. However, there is a need to slice the data by filters, which would mean writing hundreds of individual queries. I am hoping there is a way to write one parameter query and use VBA to pass my filter to it inside a loop, and then output to Excel. Thoughts?

Here is my current code:

Dim queryName1 As String
Dim myRst1 As Recordset
Dim objExcel As Object
Dim objWB As Object
Dim objWS As Object
Set objExcel = CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open _
("C:\Documents and Settings\Desktop\FFS\Template.xlsx")
Set objWS = objWB.Worksheets("National")
queryName1 = "myQuery"
Set myRst1 = Application.CurrentDb.OpenRecordset(queryName1)
With objWS
.Range("A3").CopyFromRecordset myRst1
End With
objWB.Save
Set objWS = Nothing
Set objWB = Nothing
objExcel.Quit
Set objExcel = Nothing

 

[boblarson]

Sure it is possible. A few questions to get us going -

1. Do you have a table which has all of the Regions listed? Are there only 10 regions and will there ever only be 10? I hesitate to just hard code in 10 as things change so if you have a table which has each region listed (RegionID and RegionName) that would be helpful and allow things to be easily added to in the future (or subtracted from if there is a deletion of a region).

2. What is the current SQL of the query which you would want to use for the data?

 

[armesca]

Good questions, I'll try to clarify:

1. My raw data has state-level information. I created a reference table with state/region that I link to so I can sum my data by region (you won't see this table in my SQL code because my output query is querying other queries that have already summed the data grouped by region - if I had to redo it, I would do this all in one query with subqueries, but this has been a progression with new requirements, so it's a little messy).

2. My output query gives me 30 rows of data, 3 rows for each region. I want to export one region's data automatically to a specified sheet and range in Excel. My previous code does this, but I'd like to not have to write queries for each region. For one project, I would have to write 10 queries instead of 1 parameter query, and there are multiple projects, so this is a magnitude of 10 for each project and/or report which could get quite ridiculous. Instead I'd like to leverage the ability to use parameter queries and pass the value to it (region) in my VBA loop using case statements. I think I can also apply this approach to future logic which will save tons of time and reduce potential errors.

The only part I don't have figured out is how to pass a string variable to a parameter query in VBA inside a loop and shoot that data out to Excel (also inside the loop so that my variable/query can run for every region).

Let me know if you have any more questions. Thanks a ton!

SQL:

SELECT
[qry_counts].[Region],
[qry_rawsum].[Project],
[qry_allocation].[Allocation],
[qry_rawsum].[Amount],
[qry_rawsum].[Draw Downs],
[qry_rawsum].[Balance],
[qry_rawsum].[Holds],
[qry_rawsum].[Available],
[qry_counts].[Total Count],
[qry_counts].[Hold Count],
IIf([Amount]=0,0,[Draw Downs]/[Amount]) AS [Pct of Draw Downs],
IIf([Amount]=0,0,[Balance]/[Amount]) AS [Pct of Balance],
IIf([Amount]=0,0,[Holds]/[Amount]) AS [Pct of Holds],
IIf([Amount]=0,0,[Available]/[Amount]) AS [Pct of Available],
IIf([Balance]=0,0,[Holds]/[Balance]) AS [Pct of Balance on Hold],
IIf([Balance]=0,0,[Available]/[Balance]) AS [Pct of Balance Available]
FROM
[qry_allocation]
INNER JOIN
(
[qry_counts]
INNER JOIN
[qry_rawsum]
ON
([qry_counts].[Project] = [qry_rawsum].[Project]) AND
([qry_counts].[Region] = [qry_rawsum].[Region]))
ON
([qry_allocation].[Project] = [qry_counts].[Project]) AND
([qry_allocation].[Region] = [qry_counts].[Region]);

 

[boblarson]

You should be able to do something like this (untested).

Function SendToRegion()
    Dim rsRegions As DAO.Recordset
    Dim rsOut As DAO.Recordset
    Dim objExcel As Object
    Dim objWB As Object
    Dim objWS As Object
    Dim strSQL As String
 
    Set objExcel = CreateObject("Excel.Application")
 
    Set rsRegions = CurrentDb.OpenRecordset("Select [Region] From myQuery ORDER BY [Region]", dbOpenForwardOnly)
 
    Do Until rsRegions.EOF
 
        Set objWB = objExcel.Workbooks.Open _
                    ("C:\Documents and Settings\[COLOR=red]UserNameHere[/COLOR]\Desktop\FFS\Template.xlsx")
 
        Set objWS = objWB.Worksheets("National")
 
        strSQL = "Select * From myQuery WHERE [Region] = " & rsRegions(0)
 
        Set rsOut = Application.CurrentDb.OpenRecordset(strSQL)
 
        objWS.Range("A3").CopyFromRecordset rsOut
 
        rsOut.Close
 
        objWB.SaveAs "C:\Documents and Settings\[COLOR=red]UserNameHere[/COLOR]\Desktop\FFS\Region" & rsRegions(0) & Format(Date, "yyyymmnn") & ".xlsx"
 
        objWB.Close
 
        rsRegions.MoveNext
 
    Loop
 
    Set objWS = Nothing
    Set objWB = Nothing
 
    objExcel.Quit
 
    Set objExcel = Nothing
End Function