By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,247 Members | 1,018 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,247 IT Pros & Developers. It's quick & easy.

How to dynamically assign a query field its criteria?

P: n/a
MLH
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?
Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.