| re: Is =GetMyCriteria() a valid criteria expression in query?
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" <CRCI@NorthState.net> wrote in message
news:5ue3d1d642t6pninnvde3ofr9jphuljcqg@4ax.com...[color=blue]
> 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 -[color=green]
> >= #6/10/2005# - or -[/color]
> 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?[/color]
| 
