Use a function in the "Criteria" of a query?

[Six Lombardi's]

What I need to do is pull records with certain dates in a particular
field. The reasoning is complex so I won't even try to explain it,
but just imagine you have a field called LastDate and you want to keep
all records where it is equal to 10/1/2010, 12/1/2010, 3/1/2011 or
5/1/2011. Those dates are going to vary based on the date you run
this query, but let's just focus on those dates.

I created a public function called RenDte. In this function I have
logic that sets RenDte to the above dates, i.e. RenDte = "10/1/2010,
12/1/2010, 3/1/2011, 5/1/2011" Now, in the Criteria line at the
bottom of the query view I put "RenDte()" (without the quotes). When
I try to run it, I get a mesage saying "Data type mismatch in query
expression".

Anyone got any suggestions? If I run it with just one date instead of
four, it will work. If I substitute the word "or" for the comma, it
won't work.

 

[John Spencer]

You are trying to compare a date to a string and that is generating the error.
You might be able to do this by using an expression like the following, BUT
this will be relatively slow.

Instr(RenDte,Format(LastDate,"mm/d/yyyy"))>0

In query design view that would look like
Field: Instr(RenDte,Format(LastDate,"mm/d/yyyy"))
Criteria: > 0

Other Choices:
Build the entire SQL string in VBA and use that sql string as the query or as
the source for a form or report.

For a form:
Build a filter string and filter the data for a form
RenDte would return a string as follows that could be used to filter the form
LastDate in (#10/1/2010#,#12/1/2010#,#3/1/2011#,#5/1/2011#)

For a report that you are opening using VBA you could have that same string to
filter the report
Docmd.OpenReport "NameOfReport",,,renDte()

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Bob Barrows]

What I need to do is pull records with certain dates in a particular
field. The reasoning is complex so I won't even try to explain it,
but just imagine you have a field called LastDate and you want to keep
all records where it is equal to 10/1/2010, 12/1/2010, 3/1/2011 or
5/1/2011. Those dates are going to vary based on the date you run
this query, but let's just focus on those dates.

I created a public function called RenDte. In this function I have
logic that sets RenDte to the above dates, i.e. RenDte = "10/1/2010,
12/1/2010, 3/1/2011, 5/1/2011" Now, in the Criteria line at the
bottom of the query view I put "RenDte()" (without the quotes). When
I try to run it, I get a mesage saying "Data type mismatch in query
expression".

Anyone got any suggestions? If I run it with just one date instead of
four, it will work. If I substitute the word "or" for the comma, it
won't work.

I think you should create a table to contain the desired dates and join
to that table in your query. Before running the query, populate the
table with the desired dates.

 

[Six Lombardi's]

You are trying to compare a date to a string and that is generating the error.
  You might be able to do this by using an expression like the following, BUT
this will be relatively slow.

   Instr(RenDte,Format(LastDate,"mm/d/yyyy"))>0

I used this (which is ingenius, BTW) and it gave me the results I was
looking for. Thanks for the tip!