I'm trying to use a user-defined function with the fields portion of a SELECT query, for example:

SELECT MyFunction([client_value]) AS new_client_value FROM client_info;

The function is defined as follows:

Public Function MyFunction(client_id)
If (client_id = 1) Then
MyFunction = 50
ElseIf client_id = 2 Then
MyFunction = 86
Else
MyFunction = 98
End If
End Function

Problem: When I try to run the query, I get an error claiming the function is undefined. I tried defining it within a Class module and within a Form class, but neither worked. Is this even possible, and if so, will it work within a UNION query?

Any help would be greatly appreciated. This forum has saved my hide many times.

It IS possible. Make sure that you wrote your function in a general module and defined the type of result it should return:


Public Function MyFunction(client_id As Integer) As Integer

If (client_id = 1) Then
MyFunction = 50
ElseIf client_id = 2 Then
MyFunction = 86
Else
MyFunction = 98
End If


End Function


I have the feeling that you may not have the habit of using the Option Explicit. You should. Else, access will interpret many code mistakes as new Variant varaibles.

Alex