Expression from table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that uses 10 variables to determine which expression to use to
calculate the result. Rather than set up an enormous series of IIF
statements I would like to have a variable calculation for each line of my
query that comes from a table:

Table 1:
Field1 Field2
CategoryA [Transactions]/50
CategoryB [Transactions]+50
CategoryC [Transactions] / [FieldB]

I set up a table similar to the example above, but the query will only
recognize the "expressions" as text. Is it possible to use this method, or
is their a better way to do this?
 
You can't use expressions in tables -- only in queries, forms, reports, and
VBA.

|I have a query that uses 10 variables to determine which expression to use
to
| calculate the result. Rather than set up an enormous series of IIF
| statements I would like to have a variable calculation for each line of my
| query that comes from a table:
|
| Table 1:
| Field1 Field2
| CategoryA [Transactions]/50
| CategoryB [Transactions]+50
| CategoryC [Transactions] / [FieldB]
|
| I set up a table similar to the example above, but the query will only
| recognize the "expressions" as text. Is it possible to use this method,
or
| is their a better way to do this?
 
I played around with the Eval() function but couldn't get it right. I would
recommend a function like:

Function GetCatReturn(pdblTrans As Double, _
pstrCat As String, _
Optional pdblFieldB As Double = 1) As Double
Select Case pstrCat
Case "CategoryA"
GetCatReturn = pdblTrans / 50
Case "CategoryB"
GetCatReturn = pdblTrans + 50
Case "CategoryC"
GetCatReturn = pdblTrans / pdblFieldB
End Select
End Function

Assuming you had a table with fields Transactions, Field1, and FieldB you
could write a query with sql like:
SELECT Transactions, Field1, FieldB,
GetCatReturn([Transactions],[Field1],[FieldB]) AS TheResult
FROM tblEvalTest;
 
Back
Top