User-Defined Function...How to build one

[Waxaholic]

I am trying to create a user defined function within Access. I created a new Module (Standard) and placed the following in it:

Function BlockA()
BlockA = _
IIf(CLng(IIf(IsNull([LPICHUSG]![LPANREQ]), 0, [LPICHUSG]![LPANREQ])) = 0, 0, CLng(IIf(IsNull([LPICHUSG]![LPANNONE]), 0, [LPICHUSG]![LPANNONE])) / CLng(IIf(IsNull([LPICHUSG]![LPANREQ]), 0, [LPICHUSG]![LPANREQ])))
End Function

After saving it and creating a test query to make sure it works, it returns an error of "Undefined Function "BlockA" in expression". I built the query by selecting the function from within the Expression Builder.

Can someone give me a hint as to what might be causing this. This is my first attempt at creating a function. I have been using Access for quite a while so it is not neccessarily new to me.

Thanks

 

[LTunnicliffe]

You are on the right track... but you should try doing something a little different. You should make your UDF have inputs and return the desired results. So your UDF in VBA should look something like:

Public Function BlockA(LPANREQ As Long, LPANNONE As Long) As Long

End Function

Then set up your if statement (which I'm too tired to disect) how you want it. Remember UDF's are done using VBA so IIF won't work, it's back to IF.

Hope this helps!

 

[Waxaholic]

I tried what you suggested but it still gives the same error. I even tried a simple Function like

Function fnTest()
fnTest = 1
End Function

and it still gives the same error. In the help file associated with the error it mentions going into Tool>References to make sure the UDF is referenced. Problem is, there is nothing under references associated with this UDF or any others i tried creating. I am stumped. It seems that it doesn't recognize, or cannot find, fnTest. I appreciate the help and advice though. I am not giving up, gonna do some research.

Thanks

 

[bat17]

Make sure that you save the module with a differnt name to the function or Access will throw a wobbly!

Peter