User-Defined Functions in MS Access?

  • Thread starter Thread starter yisraelharris
  • Start date Start date
Y

yisraelharris

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

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

As I am a relative novice in MS Access, I would be grateful fo
solutions which spell things out explicitly. Thank you
 
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.
Click to expand...

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.
 
Back
Top