Cannot use function in query

[RMires]

I have a user defined function that I want to use in a query, but I don't
know where to "place" the function for the query to access it. It currently
resides in a form in the same database. I can see the "Code" icon when
selecting forms, but when I select "Query" it is greyed out. I'm not very
experienced with using functions in access. Surely I can access functions in
Queries?

 

[Stefan Hoffmann]

hi,

I have a user defined function that I want to use in a query, but I don't
know where to "place" the function for the query to access it. It currently
resides in a form in the same database.

Yes, you can.

You must place your function in a standard module, not a class not form
module. You also must declare it as public, e.g.

Option Compare Database
Option Explicit

Public Function ResultForQuery(AValue As Variant) As Variant

On Local Error Goto LocalError

Dim Result As Variant

Result = Null
If Not IsNull(AValue) Then
End If

ResultForQuery = Result
Exit Function

LocalError:
'Error handling.
'Do not display any message.
'Return NULL or magic value.
ResultForQuery = Null

End Function

The parameter declaration As Variant is necessary to handle empty (Null)
fields. The type of the return value depends on your needs and the way
you like to handle Null parameters.


mfG
--> stefan <--