Passing the criteria (A2K)

Topic

New Reply

Good Morning,

Is there a way to pass the “Between Date And Date” criteria to a query in vba? I know there are a couple of ways of passing the date range to the query but don’t know how to pass it through code.

In the query grid, in the criteria section of a date, I want it to read, for example: “Between 1/1/01 and 12/31/01” ,with the BeginingDate and EndingDate being what is passed in code. Something like this:

dim dStart as Date
dim dEnd as Date

dStart = DLookup(“NewStartDate”, “qryNewDates”)
dEnd= DLookup(“NewEndDate”, “qryNewDates”)

but I don’t know, if, a) this is the correct approach, if it is, how to complete it and how to pass it to the query.

If anyone has any suggestions, I would love to hear it.

Thanks in advance.

Reply | Quote

Replies

The easiest way is to have a form into which the user can enter the appropriate dates, then in your query’s selection criteria put a reference to each control from the from, something like:

WHERE SomeDate Between Forms!frmEnterDates!txtStartDate and Forms!frmEnterDates!txtEndDate

Reply | Quote

Thanks for the suggestion Mark. I do this in most of my processess however, in this particular case I can’t. I need the criteria section to read, for example “Between 1/1/01 and 12/31/01”.

The reason it MUST include the dates and not reference to a form because this query data is for an Excel spreadsheet. Where the user will requery the data in the spreadshet and Excel doesn’t recognize anything other than the string in the criteria section.

I’m now looking at dynamic queries, it looks like this is my only opion.

However, I will use your suggestion many times over for my other projects.

Again, thank you.

Reply | Quote

You can create the SQL string in code:

Dim strSQL As String
strSQL = “SELECT Field1, Field2 FROM SomeTable WHERE SomeDate Between #” & _
Forms!frmEnterDates!txtStartDate & “# And # ” & Forms!frmEnterDates!txtEndDate & “#”

You can change the SQL of your query to this string, or pass the SQL string to Excel, depending on how you use it.

Example of changing the SQL of a query:

CurrentDb.QueryDefs(“MyQuery”).SQL = strSQL

(You need a reference to the Microsoft DAO 3.6 Object Library for this)

Reply | Quote

If you’re retrieving the data as an external query from Excel, you might find it easier to have the parameters in Excel and just have your query returning all data to MSQuery, which can then filter it according to the parameters given in Excel.

Reply | Quote

If you are constructing SQL to execute the query, the WHERE clause of the SQL string should look like :
. . . . . . .WHERE (((.) Between #1/1/2001# And #12/31/2001#));
obviously substituting your values for tablename and fieldname.
(I didn’t work this out; I wrote a query and then had a look at the SQL Access generated!)

If you are using DLookUp, you can use the criterion part of the clause (this extract from Access help):

DLookup( expr, domain, [criteria ])
The DLookup function has the following arguments.

Argument Description
expr An expression that identifies the field whose value you want to return. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field . In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.
domain A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.
criteria An optional string expression used to restrict the range of data on which the DLookup function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DLookup function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DLookup function returns a Null.

The criterion can be a complex expression like “condition 1 AND condition 2” so it could be “tablename.fieldname GE Me.StartDate and tablename.fieldname LE Me.Enddate”
I am sure this is not the whole answer but it may give you some pointers to a solution.
Hope it helps somewhat, anyway
Silverback

Reply | Quote