Custom functions in queries

  • 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
Englan
 
Hi Brian,

Where is this custom function typed? In reading your
message it sounds like you may have typed it all in the
query expression. If this is the case, you can't do it
this way. The custom function would have to be saved in
a module in your database. And, the custom function
would not refer to field values directly (such as [disc
data].[journalbatchname]). Rather, the field values
would be passed to the custom function as Arguments.

To pass Arguments, the first line of the Function
definition would look something like the following, which
assigns variable names to the arguments that are passed
to it:

Function Numbersort(JournalBatchNm) As Double

If you had more than one argument to pass, you would list
each additional one after the first, all separated by
commas.

Then, when calling the function from your query or any
other calculated control, you would type the name of your
function and the values that you want to pass. This is
where you would use the field name(s) as follows:

Numbersort([disc data].[journalbatchname])

This would pass this field value to the JournalBatchNm
variable in your custom function. All of the code in the
function would then refer to the variable name, not the
field name.

By the way, in this particular case, it looks like you
could also do what you want to do without creating a
custom function by using nested iif() functions.

Hope this helps. Post back if my assumptions were
incorrect or you would like more info.

-Ted Allen
-----Original Message-----
Hello,

I am trying to use a custom function in an access query
Click to expand...
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
Click to expand...
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)
Click to expand...
= "Reverse" Then ' Evaluate argument.
Numbersort = Right([disc data]. [journalbatchname], 6)
Else
If Left([disc data].[journalbatchname], 8)
Click to expand...
= "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
Click to expand...
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".
 
Back
Top