Update Criteria programmatically?

[Guest]

I have a query that has criteria on the REGION field. It has the default
region = 'CA'.

I want to run through a list of regions (CA, NV, WA, etc..) , update the
query criteria (via DAO 3.6 querydef) and copy the query result onto an Excel
spreadsheet.

But I can't figure out how to update the criteria. Is that possible using
DAO querydef or even ADO? I'd rather use DAO since I'm more familiar with it.
If not, what's the workaround?

Thanks!
-Mark

 

[Wayne Morgan]

There are a couple of ways to update the criteria dynamically.

1) Use a Parameter for the criteria. The value of this parameter can be
supplied by a textbox or other control on a form. It can also be supplied as
a prompt.

2) Using DAO, rewrite the SQL property of the QueryDef.

Examples:
1a) SELECT .... FROM ... WHERE Region = [Forms]![frmMyForm]![txtMyTextbox];

1b) SELECT .... FROM ... WHERE Region = [Please input the region.]

2)
Dim strSQL As String, strWhere As String
strSQL = "SELECT ... FROM ..."
strWhere = " WHERE Region = '" & InputBox("Please input the region.") & "';"
CurrentDb.QueryDefs("qryMyQuery").SQL = strSQL & strWhere

You could also concatenate in the string value from a control or other code.

 

[Guest]

Thanks Wayne. I try both routes and see what works best.

Gotta admit, I am surprised that there's no criteria section in the
querydef. If the Query Design mode can parse out the criteria for each field,
I figured they'd put it into the querydef. Oh well!

-Mark

There are a couple of ways to update the criteria dynamically.

1) Use a Parameter for the criteria. The value of this parameter can be
supplied by a textbox or other control on a form. It can also be supplied as
a prompt.

2) Using DAO, rewrite the SQL property of the QueryDef.

Examples:
1a) SELECT .... FROM ... WHERE Region = [Forms]![frmMyForm]![txtMyTextbox];

1b) SELECT .... FROM ... WHERE Region = [Please input the region.]

2)
Dim strSQL As String, strWhere As String
strSQL = "SELECT ... FROM ..."
strWhere = " WHERE Region = '" & InputBox("Please input the region.") & "';"
CurrentDb.QueryDefs("qryMyQuery").SQL = strSQL & strWhere

You could also concatenate in the string value from a control or other code.

--
Wayne Morgan
MS Access MVP

I have a query that has criteria on the REGION field. It has the default
region = 'CA'.

I want to run through a list of regions (CA, NV, WA, etc..) , update the
query criteria (via DAO 3.6 querydef) and copy the query result onto an
Excel
spreadsheet.

But I can't figure out how to update the criteria. Is that possible using
DAO querydef or even ADO? I'd rather use DAO since I'm more familiar with
it.
If not, what's the workaround?

Thanks!
-Mark

 

[Wayne Morgan]

There is a Parameter property of a QueryDef object. The problem is going to
be exporting the query to Excel once you use that. It works fine within
code, but if you open the query for the export to Excel, it is a different
copy of the query than the one you've been working with in code using a
QueryDef object variable. I tried to think of a way to export the object
variable to Excel, but couldn't come up with an easy way (assuming you're
using TransferSpreadsheet). There may be a way using ADO, but I haven't done
much work with ADO. I've heard that it is possible to open a recordset in
ADO and use it as the source of another statement. If so, this may be able
to be used as the table source in a TransferSpreadsheet statement, but I
don't know that for sure.