Changing query criteria through VBA

[Jake F]

I have a query that I need to send to excel, but it needs to be grouped by
two things, region and day/night. It would take ten different queries to get
the differnent sheets that I'd need, is there a way to use one main query and
then change the criteria for those two columns to get the 10 excel workbook
sheets? Thanks.

 

[Allen Browne]

Assign the SQL property of the QueryDef.

This kind of thing:

Dim strWhere As String
Dim strSql As string

strWhere = "(SomeField = " & SomeNumber & ")"
strSql = "SELECT Table1.* FROM Table1 WHERE " & strWhere & " ORDER BY
MyField;"
CurrentDb.QueryDefs("MyQuery4Export").SQL = strSql

 

[Jake F]

I'm getting the "Item not in this collection" error. Also I need to change
two of the fields in the query, how would I do that in the strWhere? Here's
what I'm working with, maybe you can see why it's not completing. The
exports will run themselves but not when the criteria change part is
inserted. Thanks.

Public Function ERN_Output()
On Error GoTo Err_ERN_Output

Dim strFilePath As String
Dim strFileName As String
Dim strOutputFile As String
Dim strMessage As String
Dim strOutputQuery As String

Dim strWhere1 As String
Dim strSql1 As String
Dim strWhere2 As String
Dim strSql2 As String

strFilePath = "H:\Projects\ERN"
strFileName = "ERN " & Format(Date, "yyyy-mm-dd") & " -- PCA Alertcast
List.xls"
strOutputFile = strFilePath & "\" & strFileName
strOutputQuery = "qryERN_Information"

strWhere1 = "(Time = " & "Night" & ")"
strSql1 = "SELECT qryERN_Information.* FROM qryERN_Information WHERE " &
strWhere & _
"ORDER BY Time;"

strWhere2 = "(Time = " & "Day" & ")"
strSql2 = "SELECT qryERN_Information.* FROM qryERN_Information WHERE " &
strWhere & _
"ORDER BY Time;"

'************************ Create Files *************************

'Sheet 1
CurrentDb.QueryDefs("strOutputFile").SQL = strSql1
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel8, _
strOutputQuery, _
strOutputFile, _
True, _
"Sheet1"

'Sheet 2
CurrentDb.QueryDefs("strOutputFile").SQL = strSql2
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel8, _
strOutputQuery, _
strOutputFile, _
True, _
"Sheet2"

'************************ Format Worksheets 1&2 *************************

'Set variables to format the download
Dim objXLApp As Object
Dim objXLBook As Object
Dim objXLSheet1 As Object
Dim objXLSheet2 As Object

'Set the objects to format
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open(strFilePath & "\" & strFileName)
Set objXLSheet1 = objXLBook.Worksheets("Sheet1")
Set objXLSheet2 = objXLBook.Worksheets("Sheet2")

'Sheet 1
'Bold
objXLSheet1.range("A1:G1").Font.Bold = True
'Autofit columns
objXLSheet1.range("A:G").columns.autofit

'Sheet 2
'Bold
objXLSheet2.range("A1:G1").Font.Bold = True
'Autofit columns
objXLSheet2.range("A:G").columns.autofit

 

[Douglas J. Steele]

A couple of things. First, rename your field: Time is a reserved word, and
shouldn't be used as a field name. For a comprehensive list of names to
avoid (plus a link to a free utility to check your application for
compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

Second, presumably your Time field is a text field that contains either
"Day" or "Night", correct? If that's the case, you need to ensure that
you're comparing the field to a text string:

strWhere1 = "(Time = 'Night')"

strWhere2 = "(Time ='Day')"

(What you have implies that the variable is supposed to be compared to a
table field named Night, or a field named Day)

If your Time field doesn't contain a string, please provide more information
about it.

 

[Jake F]

I updated the strWhere and changed the field name in the table. I'm still
getting the error though. Would this be how to change two criteria or would
i need a different string for each?

strWhere1 = "(Day_Night = 'Day')" & "(Cnty_Cd = '00')"
strSql1 = "SELECT qryERN_Information.* FROM qryERN_Information WHERE " &
strWhere1 & _
"ORDER BY Day_Night;"

'************************ Create Files *************************

'Sheet 1
CurrentDb.QueryDefs("strOutputFile").SQL = strSql1
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel8, _
strOutputQuery, _
strOutputFile, _
True, _
"Sheet1"

 

[Douglas J. Steele]

strWhere1 = "(Day_Night = 'Day') And (Cnty_Cd = '00')"

 

[Jake F]

Any idea why i'm still getting the "Item not found in this collection" error?
The spreadsheets export without the strWhere1/strSql1 portion.

strWhere1 = "(Day_Night = 'Day') And (Cnty_Cd = '00')"
strSql1 = "SELECT qryERN_Information.* FROM qryERN_Information WHERE " &
strWhere1 & _
"ORDER BY Day_Night;"

'************************ Create Files *************************

'Sheet 1
CurrentDb.QueryDefs("strOutputQuery").SQL = strSql1
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel8, _
strOutputQuery, _
strOutputFile, _
True, _
"Sheet1"

 

[Douglas J. Steele]

It should be CurrentDb.QueryDefs(strOutputQuery).SQL = strSql1 (no quotes).
Access is looking for a query named strOutputQuery, as opposed to
qryERN_Information, the value stored in variable strOutputQuery.

Sorry, I should have noticed that in your earlier code.

 

[Jake F]

Works great! I had to change the sql to the tblERN_Information because it
created a circular reference in the query when I had qryERN_Information.
Thanks!

 

[David W. Fenton]

Any idea why i'm still getting the "Item not found in this
collection" error?

That usually indicates that you are referring to a field by an
incorrect name.