Dynamic field name in Access query and function, can this be done?

Question

in an access database i have created a function which compares 3 fields in my query:

Fields:
CostMacDON
CostKentFRY
CostBurgKIN
TurnMacDON
TurnKentFRY
TurnBurgKIN
CustMacDON
CustKentFRY
CustBurgKIN

Public function Eval(MacD, KentF, BurgK)
'Note: the real evaluation is a bit more complex, this is just an oversymplified example
if MacD>KentF and MacD>BurgK
 Eval="MD is the highest"
else
 Eval="MD is NOT the Highest"
endif

end function

to call the function in a access query view i use

Evaluate Cost: Eval(CostMacDON, CostKentFRY, CostBurgKIN)
         ====       ----        ----         ----
Evaluate Turn: Eval(TurnMacDON, TurnKentFRY, TurnBurgKIN)
         ====       ----        ----         ----
Evaluate Cust: Eval(CustMacDON, CustKentFRY, CustBurgKIN)
         ====       ----        ----         ----

BUT...with all this repetition of Cost/Turn/Cust, I was hoping to simplify the queries by adapting the function. After some research, it seems that I should restructure the tables which I really want to avoid. (as this is just a very small part of the puzzle)

I want the users to be able to add other field, so I would also like to avoid complex sql statements. SO.. I would like to call the function like this

Evaluate Cost: Eval("Cost")

Public function Eval(EvalType as variant)
Dim MacD as Variant  (??)
Dim KentF as Variant  (??)
Dim BurgK as Variant  (??)
MacD= EvalType & "MacDON"
KentF= EvalType & "KentFRY"
BurgK= EvalType & "BurgKIN"

' this however gives me the names of the 3 fields, and I want to compare the content !?!

if MacD>KentF and MacD>KentF
 Eval="MD is the highest"
else
 Eval="MD is NOT the Highest"
endif

end function

Does anybody have an idea if this can be done?

Answer 1

You would have to open a recordset or use DLookup() aggregate function to pull data from table into function.

Probably also need to pass into function ID of record.

Public Function Eval(EvalType As Variant, varID As Variant) As String

Example with DLookup():

MacD = DLookup(EvalType & "MacDON", "tablename", "ID=" & varID)

Answer 2

Nothing in your code shows any interaction with tables/recordsets? If you are asking how to retrieve values from fields in a table where the field names are not fixed but can vary dynamically, then that could be done by passing the field name as a variable to eg an open recordset.

Eg you can do

MyField$ = "MacDCost" 
MyValue = MyRecordSet(MyField)

And you can play with MyField$ to your heart's content. Eg:

Dim MyRSt as Recordset
Dim Shops(3) As String
Dim Type(3) As String
Dim ShopTeller as Long
Dim TypeTeller as Long
Dim MyValue as Double

Shops(0)="MacD"
Shops(1)="BurK"
Shops(2)="KenF"
MyType(0)="Cost"
MyType(1)="Turn"
MyType(2)="Cust"

Set MyRst=CurrentDB.OpenRecordset("MyTable")
MyRst.MoveFirst

TypeTeller = 0 
    Do
        MyValue = 0
        ShopTeller = 0
        Do
            myValue = MyValue + CDbl(MyRst(Shops(ShopTeller) & MyType(TypeTeller)))
            ShopTeller = ShopTeller + 1
        Loop Until ShopTeller = UBound(Shops)
        Msgbox "Total " & MyType (TypeTeller) & "from all junk food outlets is :" & MyValue, vbExclamation & VbOKOnly
        TypeTeller = TypeTeller + 1
    Loop Until TypeTeller = UBound(MyType)

MyRst.Close
Set MyRst = Nothing 

However, if you want the user to be able to add/remove 'shops' etc then you really cannot keep that info in the same table - you will need to, for instance, have a table which contains the Shops and a table which contains the other data (replacing the 2 arrays above) and then link them through an ID field and use JOINs to retrieve the values.