User-Defined Functions in MS Access?

I am building a query, and besides various table columns, I need it to
output a string whose value depends on a non-straightforward algorithm
based on the values of other columns.

If I were in SQL-Server, I would simply write and invoke a user-defined
function. In MS Access, what does one do?

As I am a relative novice in MS Access, I would be grateful for
solutions which spell things out explicitly. Thank you.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

On Thu, 3 Jun 2004 03:39:32 -0500, yisraelharris
wrote:


I am building a query, and besides various table columns, I need it to
output a string whose value depends on a non-straightforward algorithm
based on the values of other columns.

If I were in SQL-Server, I would simply write and invoke a user-defined
function. In MS Access, what does one do?

As I am a relative novice in MS Access, I would be grateful for
solutions which spell things out explicitly. Thank you.

On the Modules tab on the database window, create a new Module. You
can write a function in VBA - the syntax is different than T-SQL but
if you're familiar with Visual Basic, it's just another dialect.
Specify

Public Function MyFunctionName(arg1 AS Variant, arg2 AS Variant) As
Variant
your expressions here
MyFunctionName = the final result of your algorithm
End Function

In the Query you can then type

FuncResult: MyFunctionName(thisfield, thatfield)

to perform the calculation.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public