Custom Functions 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 Taylor 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".

Please help....!

Thanks

Brian Taylor
Manchester
England
Click to expand...

Assuming you defined this function in a standard module, I imagine the
problem lies in your references to [disc data].[journalbatchname].
Where is this supposed to come from? If this is a field in the query,
the function won't know anything about it unless you pass it as an
argument. But the only argument you're passing is called "value",
though I don't see where you're using that at all. I could envision
your rewriting the function as

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

.... and call it from the query like this:

expr2: Numbersort([disc data].[journalbatchname])

But that's only assuming I've guessed correctly at what you're trying to
do. If I haven't, please explain in more detail.
 
Back
Top