Dynamic Queries

[Zequel]

After years of dealing with Access queries, I've found, sometimes, the best way to set query criteria is with a custom function.
Let's say we have a query that needs a beginning date that's stored in a table that's called tblReportCriteria.
All I want is that one date (the table is a single-row table) which is stored in the BegDate field to use in my query.
The custom function is below:

Function ReportBegDate() As Date
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblReportCriteria", dbOpenSnapshot)
If rst.EOF = False Then
If IsDate(rst!BegDate) Then ReportBegDate = rst!BegDate
End If
rst.Close
db.Close
End Function

Now, I can use ReportBegDate() as a value in my criteria line in the query builder. This method is very quick for returning a single value but you can also use it to return a value based on a row's data if you give the function a parameter i.e. ReportBegDate(ID As Product). In this case it may slow down your query.

 

[JeremyNYC]

Z,

Nice. I don't know that I've ever used a function as a criteria. One thing, though--I'd make sure to pull just the field you need, not all of the fields of the table. That will make it even faster.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.