Is =GetMyCriteria() a valid criteria expression in query?

[MLH]

Am repeating question with different subject heading, perhaps
stating more clearly my problem...

I have an A97 query (qryVehiclesNowners2) that has a table
field in it named [DateLeft]. Depending on the selections made
in a number of criteria choices on a form, a field on the form
will have string values in it like...

Between #12/1/2004# And #12/31/2004# - or -

= #6/10/2005# - or -

Between #6/1/2005# And #6/30/2005#

I want to use them as criteria for the query.

There's a combo-box on the form whose row-source is the
above named query, qryVehiclesNowners2. How do I tell the
query that the criteria for that field is sitting in
Forms!MyForm!MyCrit waiting to be read 'n used?

I tried this on the criteria row in the QBE grid...
=[Forms]![frmLookUpAvehicle]![TimeCrit]

But I get a message saying "This expression is typed incorrectly,
or it is too complex to be evaluated. For example, a numeric ex-
pression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression
to variables." The query runs fine if I delete only that field or
simply remove the reference criteria.

So I deleted the criteria, created a global var to hold the criteria
string value and a procedure in a standard module to return the
global var's value when called. Then I put the following criteria
in the query's [DateLeft] field:
=GetMyCriteria()
However, the query still pukes with the same error.

If I cut 'n paste the string from frmLookUpAvehicle's TimeCrit
textbox into the query, it runs as expected. So there's nothing
wrong with the syntax of the criteria string. Just seems it cannot
be referenced in the query - must be typed in there explicitly.

Someone know the answer?

[Wayne Morgan]

When you place the criteria the way you are doing it, the criteria isn't
being evaluated for what it says, it is being treated as a string. One way
around this is to modify the query using code.

Example:
Dim strSQL As String, strWhere As String
strSQL = "SELECT...." 'the part of the query that doesn't change
strWhere = Me!DateLeft
CurrentDb.QueryDefs("qryVehiclesNowners2").SQL = strSQL & " " & strWhere &
";"

--
Wayne Morgan
MS Access MVP
"MLH" <CR**@NorthState.net> wrote in message
news:5u********************************@4ax.com...

Am repeating question with different subject heading, perhaps
stating more clearly my problem...

I have an A97 query (qryVehiclesNowners2) that has a table
field in it named [DateLeft]. Depending on the selections made
in a number of criteria choices on a form, a field on the form
will have string values in it like...

Between #12/1/2004# And #12/31/2004# - or -

= #6/10/2005# - or -

Between #6/1/2005# And #6/30/2005#

I want to use them as criteria for the query.

There's a combo-box on the form whose row-source is the
above named query, qryVehiclesNowners2. How do I tell the
query that the criteria for that field is sitting in
Forms!MyForm!MyCrit waiting to be read 'n used?

I tried this on the criteria row in the QBE grid...
=[Forms]![frmLookUpAvehicle]![TimeCrit]

But I get a message saying "This expression is typed incorrectly,
or it is too complex to be evaluated. For example, a numeric ex-
pression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression
to variables." The query runs fine if I delete only that field or
simply remove the reference criteria.

So I deleted the criteria, created a global var to hold the criteria
string value and a procedure in a standard module to return the
global var's value when called. Then I put the following criteria
in the query's [DateLeft] field:
=GetMyCriteria()
However, the query still pukes with the same error.

If I cut 'n paste the string from frmLookUpAvehicle's TimeCrit
textbox into the query, it runs as expected. So there's nothing
wrong with the syntax of the criteria string. Just seems it cannot
be referenced in the query - must be typed in there explicitly.

Someone know the answer?

[MLH]

Thank-you so much, Wayne, for your generous reply.
That's precisely what I had to do. I could not get past
the block I described. So, I recreated the entire query
in code, concatenating all the parts together and set
my combo-box's RowSource property to that after it
was properly constructed. Worked like a charm.

Thx again.

[David Schofield]

On Mon, 11 Jul 2005 18:36:46 -0400, MLH <CR**@NorthState.net> wrote:

Thank-you so much, Wayne, for your generous reply.
That's precisely what I had to do. I could not get past
the block I described. So, I recreated the entire query
in code, concatenating all the parts together and set
my combo-box's RowSource property to that after it
was properly constructed. Worked like a charm.

Thx again.

Hi
Honest but tactless if you do this too often!
It reads like "whenever I have a problem I post a question first and
then look for the answer"
David