Help to optimize a (simple) function

[halx]

Hello,
Could someone help me to optimize a simple function
(well, the function's logic is simple, but the optimization might not be simple to find)

Suppose you have
- a table "tblMyTable", with a field called "field1".
- a function to apply to this field.
That is, I want to do:
SELECT myFunction(field1) from MyTable

The problem is that the function is defined by the user.

Before I had something like this:
----------------------------------
Sub myFunction(str as String)
Select Case str
Case A
myFunction = ...
Case B
....
End sub
----------------------------------

Simple, but since the user should be able to easily define the function (without "programming" in VBA ), I switched to:
something like:
---------------------------------------------------------
Sub myFunction(str as String)
myFunction = Dlookup("Result", "tblMyFunction ", "value = '" & str & "'&quot
End sub
--------------------------------------------------------
That is: the user defines the function through an input form that is bound to the table "tblMyFunction".


This second implementation is damn slow when tblMyTable is big.

Does anybody have an idea for a better implementation
(that still allows the user to easily define the function) ?

Thanks,


Alx

 

[grtammi]

Hi halx:

Are your users defining the actual function (ie the programming behind the function), or just some parameters for the function?

If the latter is true (and I hope it is) try checking out faq705-2205 for a little assistance.

HTH

Greg

 

[MichaelRed]

look into the Eval function.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[halx]

Hello,

Greg: my users choose the input value of the function from a predefined list and choose the output value (whatever string they want).
Example: they can decide to associate "dog" with "12212", or "computer" with "dfsfdsafsdaf"

I'm gonna have a look at the link you told me about.

Michael: I'm sorry but I really don't see how I can use the Eval function for my problem.

Thanks

Alex

 

[grtammi]

Is the output list pre-defined also? If so, why not try using 2 list boxes on the form (call them InputListBox and OutputListBox, for this example).

Your query could simply reference the appropriate column in each one of the list boxes. Then, the only need for coding that I can see is error-checking - make sure the user actually selected something in each list box before allowing the query to run.

HTH

Greg