Custom Function in a Query...HELP!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I am trying to use a custom function in an access query and I am very stuck when it come to using/calling it in a query
When I try and execute the query with the custom formular typed in I get the prompt "Compile Error - External name not defined"

The formular is created using the following code:

Function Numbersort(value) As Doubl
If Left([disc data].[journalbatchname], 7) = "Reverse" Then ' Evaluate argument
Numbersort = Right([disc data].[journalbatchname], 6
Els
If Left([disc data].[journalbatchname], 8) = "Reverses" Then ' Evaluate argument
Numbersort = Right([journalbatchname], 6
Els
Numbersort = Mid([disc data].[journalbatchname], 25, 6
'Exit Function ' Exit to calling procedure
End I
End Functio

Please can you tell me what I need to do to get this to work. In the query I type in the following "expr2: Numbersort([value])". When I execute I then get the prompt "Compile Error - External name not defined"

Please help....

Thank

Brian Taylo
Mancheste
England
 
Brian said:
Hello,

I am trying to use a custom function in an access query and I am very stuck when it come to using/calling it in a query.
When I try and execute the query with the custom formular typed in I get the prompt "Compile Error - External name not defined".

The formular is created using the following code: -

Function Numbersort(value) As Double
If Left([disc data].[journalbatchname], 7) = "Reverse" Then ' Evaluate argument.
Numbersort = Right([disc data].[journalbatchname], 6)
Else
If Left([disc data].[journalbatchname], 8) = "Reverses" Then ' Evaluate argument.
Numbersort = Right([journalbatchname], 6)
Else
Numbersort = Mid([disc data].[journalbatchname], 25, 6)
'Exit Function ' Exit to calling procedure.
End If
End Function

Please can you tell me what I need to do to get this to work. In the query I type in the following "expr2: Numbersort([value])". When I execute I then get the prompt "Compile Error - External name not defined".
Click to expand...


You should compile you code before you try to run it. The
compiler messages are a little better at highlighting the
problems. In this case it's complaining that you used
[disc data].[journalbatchname]
with telling the compiler where to get the value of that
term. You might alse get a message about that being invalid
syntax.

The problem is that you're expecting the function to be
aware of the fields in the query. Since you never used the
function's argument, [value], it's hard to say what this
should be, but I'll take a guess that the function should
look more like:

Function Numbersort(value) As Double
If Left(value, 7) = "Reverse" Then ' Evaluate argument.
Numbersort = Right(value, 6)
Else
Numbersort = Mid(value, 25, 6)
End If
End Function

and call it from the query like this:
expr2: Numbersort([disc data].[journalbatchname])
 
Back
Top