Evaluating a where clause stored as a field in the current record.

[CautionMP]

I am trying to build a query that classifies 95 odd transaction types into 12 classifications, the problem that I have is there are four TransactionTypes that are classified differently depending on other fields in the record, in the table Classification I have a field (Exclude) that contains the expression (as it would be written in the QBE pane) and have tried everything to get the query to evaluate the expression in the Exclude field with no luck. I tried using the Eval() function with every combination of punctuation I could think of, and cannot figure out how to pass an entire record to a function so here I am.

A couple of notes, the Exclude expression does include the same record fields on all four records, and the expressions only contain inequality operators or the IsNull() function.

Thank you in advance for any input you can provide.

 

[RickSpr]

Try this:

Create a function MyEval that returns a Boolean and takes one parameter for the Exclude string plus one parameter for each column mentioned in any Exclude string. The parameters in the function heading must have the same names as the corresponding columns. For example:

    Public Function MyEval(Criteria, Column1, Column2, Column3) As Boolean
        If IsNull(Criteria) then
            MyEval = True
        Else
            MyEval = Eval(Criteria)
        End If
    End Function

Then, in your WHERE clause, use "...AND MyEval(Exclude, Column1, Column2, Column3)".

For records where the Exclude field isn't relevant, I assume it will be Null. The MyEval function just returns True in this case.

I'm speculating that Eval() won't work in the SQL code because the VBA Expression Service (which is what Jet calls to handle expressions it doesn't recognize, namely, the Eval() function call in this case) isn't getting passed the columns. The columns aren't passed because their names are in a Text variable in Jet, and not recognized as column names.

By explicitly using the column names in the WHERE clause's MyEval() function call, you should be able to get them passed to your function, where they become variables in the function's local scope. Eval() should be able to find them there.

Note that you pass all the columns, even though the Exclude expression might not refer to all of them for any given record.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein