Another Function as Criteria Problem...

[DerekMcDonald]

Hi! It seems like this should be simple, but...

I have a permanent query where I'm trying to set the criteria through functions that grab data from a form. Let's take the [Date] field as an example. In the query, the function getDate() is in the criteria field.

If the user chooses &quot;Before&quot; in cmbDate, I'd like the function to send &quot;<#&quot; & me.txtDate & &quot;#&quot; to the query's criteria field.
If I only send me.txtDate, it works fine. But if I try to send a concatenated value, I get &quot;Run time error 2001. You cancelled the previous operation.&quot; Typing <#1/1/00# directly into the query's criteria field does not generate errors, so in theory the string should look like what I'm trying to send.


Here's my code:

In a public module:
Public varDate as Variant

Public Function getDate()
getDate = varDate
End Function

In the form's module:

Private Sub cmdQuery_Click()

If Me.cmbDate = &quot;Before&quot; Then

varDate = &quot;<&quot; & &quot;#&quot; & Me.txtPurDate & &quot;#&quot;

End If

DoCmd.OpenQuery &quot;qryParams&quot;, acNormal, acEdit

End Sub

The DoCmd line brings the whole thing to a halt.

So how do I correctly set the query's criteria based on a function without generating these errors?

This is driving me crazy! Thanks!

 

[MichaelRed]

AFAIK, you cannot do what you want in this manner. To achieve this, you would need to instantiate the query as a &quot;QueryDef&quot; object and set the SQL property of the query def in the module. The problem is in attempting to include the evaluation (ye olde &quot;<&quot in the ctriteria.

The &quot;QueryDef&quot; is not all that much harder than what you are already doing, except that you need to define (DIM) and Instantiate (Set) the db and the querydef as well as release them in your module. All is at least addressed in the help system, so look it over and try it out.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over