Access 2002, Running & Saving an SQL Query in VBA 1

[13badar]

I have this Filtering system that creates custom queries. The form has combo boxes for users to input criteria which they specify for a Query.
I want to save the Query as well.
For example, following values are entered by the user:
BranchName = Blossom
Year = 2005
Month = 12
It will Pull those values from the Query only and will save that Query as "Blossom" which is the Branch Name and is the value of a combo box named cboBranch.Value
[blue]
OBJ: -I want to Run the Query based on that Criteria
-I want Access to save that Query
-The name of that Query should be the same as
the value in the combo box cboBranchValue.
[/blue]

Private Sub cmdGo_Click()

Dim BranchName As String
Dim Month As Integer
Dim Year As Integer
Dim SQL As String
Dim db As Database

BranchName = cboBranch.Value
Month = cboMonth.Value
Year = cboYear.Value

SQL = SQL & "SELECT A.BID, A.CID, A.Branch, A.Month, "
SQL = SQL & "A.Year, A.[Total Members], A.PST, A.EBT, "
SQL = SQL & "(PST-EBT)/EBT As CHANGE, "
SQL = SQL & "(PST-EBT)*[Total Members]*12 As VARIANCE"
SQL = SQL & "FROM BranchInfoTable As A"
SQL = SQL & "WHERE A.Month =" & Month & ""
SQL = SQL & " And A.Year =" & Year & ""
SQL = SQL & " And A.Branch =" & Branch & ""
SQL = SQL & "ORDER BY A.PID;"

Set db = CurrentDb
db.QueryDefs("Blossom Query").SQL = SQL

End Sub

 

[PHV]

Private Sub cmdGo_Click()
Dim BranchName As String
Dim SQL As String
Dim db As Database
BranchName = cboBranch.Value
SQL = "SELECT A.BID, A.CID, A.Branch, A.Month, "
SQL = SQL & "A.Year, A.[Total Members], A.PST, A.EBT, "
SQL = SQL & "(PST-EBT)/EBT As CHANGE, "
SQL = SQL & "(PST-EBT)*[Total Members]*12 As VARIANCE"
SQL = SQL & " FROM BranchInfoTable As A"
SQL = SQL & " WHERE A.Month=" & cboMonth.Value
SQL = SQL & " And A.Year=" & cboYear.Value
SQL = SQL & " And A.Branch='" & BranchName & "'"
SQL = SQL & " ORDER BY A.PID;"
Set db = CurrentDb
db.CreateQueryDef BranchName & " Query", SQL
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[13badar]

Thanx, it works....

 

[13badar]

I want to run the following Query after I ran the first one listed just above. The following Query is based on the first Query as it uses the Query, I already ran and saved using the Filtering Mechanism from the Form I used. I want to compare a table with the Query I just ran. I want to join The Query I Just ran with another Table that I have saved already in the same Access Database.

BranchName in RED refers to the Branch Name that was used in the Query Above.
For example, The Branch Name was Blossom and the Statement above saves a Query named "Blossom Query", I want to use that Query "Blossom Query" as my "BranchName Query below. I want to connect the whole thing to my filtering Form. When I run the Form, based on the criteria entered by me, a Query named "Branch Query" is saved. I want to use the same "Branch" name and create another Query.

The word BranchName is static and its value is changed as the value of the Combo Box cboBranch changes. Note that the tabel I'm comparing this Query to also has BranchName in its name. The table I want to Join to is named BranchNameTable. I would want to replace the BranchName in both my Table and Query Title with the cboBranch.Value.

SQL = "SELECT A.CID, A.CustName, B.CID , B.CustName, "
SQL = SQL & "IIf(A.CustName=B.CustName,""Yes"",""No"") AS MATCH"
SQL = SQL & " FROM [BranchName Query] AS A LEFT JOIN BranchNameTable AS B"
SQL = SQL & " ON A.CID = B.CID"
SQL = SQL & " UNION SELECT Null, Null, B.CID, B.CustName, ""No"""
SQL = SQL & " FROM [BranchName Query] AS A RIGHT JOIN BranchNameTable AS B"
SQL = SQL & " ON A.CID = B.CID"
SQL = SQL & " WHERE A.CID Is Null;"

BranchName = cboBranch.Value
db.CreateQueryDef BranchName & " MATCH Query", SQL

 

[PHV]

You want this ?
BranchName = cboBranch.Value
SQL = "SELECT A.CID, A.CustName, B.CID , B.CustName, "
SQL = SQL & "IIf(A.CustName=B.CustName,'Yes','No') AS MATCH"
SQL = SQL & " FROM [" & BranchName & " Query] AS A LEFT JOIN [" & BranchName & "Table] AS B"
SQL = SQL & " ON A.CID = B.CID"
SQL = SQL & " UNION SELECT Null, Null, B.CID, B.CustName, 'No'"
SQL = SQL & " FROM [" & BranchName & " Query] AS A RIGHT JOIN [" & BranchName & "Table] AS B"
SQL = SQL & " ON A.CID = B.CID"
SQL = SQL & " WHERE A.CID Is Null;"

db.CreateQueryDef BranchName & " MATCH Query", SQL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[13badar]

yes thanx, it did work. I was making a SYNTAX mistake in naming both Queries inside the SQL Query.

 

[13badar]

I was trying to save the First Query I did in Excel. Im having problems with my line of code. Can You please take a look at it and tell me whats wrong, thanx.

DoCmd.TransferSpreadsheet scImport, acSpreadsheetTypeExcel8, BranchName & "_0" & Month & " Query", _
[C:\Documents and Settings\Desktop\OUTPUT\Company Query.xls], Yes, , []

I dont know what I should put in the last three options:
[Has Field Names], [Range], [UseOA]

Also, Is there a way to save the Query in the following way where each BranchName could have a separate Spreadsheet Name:

DoCmd.TransferSpreadsheet scImport, acSpreadsheetTypeExcel8, BranchName & "_0" & Month & " Query", _
[C:\Documents and Settings\Desktop\OUTPUT\ & BranchName & "_0" & Month & " Query".xls], Yes, , []

The above Query, if it works, will save (if BranchName= Blossom, and Month=07) Blossom07 Query as Blossom07 Query in Excel and so on for all other Branches. If it's not possible, please let me know, because I've been trying different ways to make it work, thanx.

 

[13badar]

Can I run a Macro instead with the Above File Name in the File Name field, Would that Work???

 

[PHV]

You want this ?
DoCmd.TransferSpreadsheet acExport, , BranchName & "_0" & Month & " Query", _
"C:\Documents and Settings\Desktop\OUTPUT\" & BranchName & "_0" & Month & " Query.xls", True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[13badar]

Yeah, but its not saving it in that OUTPUT Folder.

 

[13badar]

Even this statement doesnt work:

DoCmd.TransferSpreadsheet acExport, , BranchName & "_0" & Month & " Query", _
"C:\Documents and Settings\Desktop\OUTPUT\Query.xls", True

 

[PHV]

Even this statement doesnt work
Any error message ?
Does BranchName & "_0" & Month & " Query" really exists ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[13badar]

sorry sorry,...i was testing the statement in a wrong CMD Click Function, o' It works.
Still confused as to Why U put True instead of answering the three other options at the end of the statement.

 

[13badar]

[blue]The following command button when clicked, saves the Query in Access and exports that Query to Excel.
I get a Run-Time Error '424' "Object Required".
I set A.BranchName as MSA_ARRAY(Iteration).Name
BranchName is what I want to pull from the Main SQL Query and save it as an Excel Worksheet with the worksheet name specified as BranchName Query. I want to save all BranchNames as separate worksheets in the same Excel File Path.[/blue]


Private Sub cmdExport_Click()

Dim ReportType As String
Dim Path As String
Dim SQL As String
Dim db As Database
Dim MSA_ARRAY
Dim Counter As Integer
Dim Iteration As Integer

ReportType = cboReport.Value
MSA_ARRAY = Array("Altamont", "Castanoan", "Blossom")
Counter = 0
Iteration = -1
Do While Counter <> 4
Counter = Counter + 1
Iteration = Iteration + 1

SQL = "SELECT A.PID, A.CID, A.month, A.contractyear, "
SQL = SQL & "A.[AM/SE], A.[members], A.[Final PMPM], "
SQL = SQL & "A.BranchName, B.Report"
SQL = SQL & " FROM tbl1 AS B INNER JOIN tbl2 AS A"
SQL = SQL & " ON B.BranchName = A.BranchName"
SQL = SQL & " WHERE B.Report='" & ReportType & "'"
SQL = SQL & " And A.month>=" & cboBegMonth.Value
SQL = SQL & " And A.month<=" & cboEndMonth.Value
SQL = SQL & " And A.contractyear>=" & cboBegYear.Value
SQL = SQL & " And A.contractyear<=" & cboEndYear.Value
SQL = SQL & " and A.BranchName=" & MSA_ARRAY(Iteration).Name
SQL = SQL & " Order by A.PID"

Set db = CurrentDb
db.CreateQueryDef MSA_ARRAY(Iteration).Name & " Query", SQL
Path = txtPath.Value
DoCmd.TransferSpreadsheet acExport, , MSA_ARRAY(Iteration).Name & " Query", "" & Path & "", True

Loop

MsgBox "All branches are saved in the C drive", vbInformation

End Sub

 

[PHV]

From where cames to you the idea that an array element has a .Name property ?
1) SQL = SQL & " and A.BranchName='" & MSA_ARRAY(Iteration) & "'"
2) db.CreateQueryDef MSA_ARRAY(Iteration) & " Query", SQL
3) DoCmd.TransferSpreadsheet acExport, , MSA_ARRAY(Iteration) & " Query", "" & Path & "", True
No problem with Path ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244