C
Clint Marshall
I'm writing a function that is in a module in Access XP. The function is
called by a select query.
The function returns a value out of a table depending on the value of the
parameter Month, which is supplied to the function by the query.
When I run the query, I get an error stating "External Name Not Defined" and
the debugger highlights the first possible value from the table "ACTUAL1".
When I change all the result values to be fixed numbers (for instance,
"Value=6"), the function runs properly.
It appears that the function doesn't know where to find the values for
ACTUAL1-ACTUAL12. These are all in the source table for the query, but the
table is not referred to in the function.
Do I need to reference or open the table within my function? If I do, will
the function know which record it is processing to return the proper value?
Here's the module with the function in it:
--------------
Option Compare Database
Option Explicit
Public Function MonthActual(Month As Integer) As Currency
Dim Value As Currency
Select Case Month
Case Is = 1
Value = [ACTUAL1]
Case Is = 2
Value = [ACTUAL2]
Case Is = 3
Value = [ACTUAL3]
Case Is = 4
Value = [ACTUAL4]
Case Is = 5
Value = [ACTUAL5]
Case Is = 6
Value = [ACTUAL6]
Case Is = 7
Value = [ACTUAL7]
Case Is = 8
Value = [ACTUAL8]
Case Is = 9
Value = [ACTUAL9]
Case Is = 10
Value = [ACTUAL10]
Case Is = 11
Value = [ACTUAL11]
Case Is = 12
Value = [ACTUAL12]
End Select
MonthActual = Value
End Function
------------
Here's the function call from the query:
Actual: MonthActual(MonthIn)
Thank you!
-Clint Marshall
called by a select query.
The function returns a value out of a table depending on the value of the
parameter Month, which is supplied to the function by the query.
When I run the query, I get an error stating "External Name Not Defined" and
the debugger highlights the first possible value from the table "ACTUAL1".
When I change all the result values to be fixed numbers (for instance,
"Value=6"), the function runs properly.
It appears that the function doesn't know where to find the values for
ACTUAL1-ACTUAL12. These are all in the source table for the query, but the
table is not referred to in the function.
Do I need to reference or open the table within my function? If I do, will
the function know which record it is processing to return the proper value?
Here's the module with the function in it:
--------------
Option Compare Database
Option Explicit
Public Function MonthActual(Month As Integer) As Currency
Dim Value As Currency
Select Case Month
Case Is = 1
Value = [ACTUAL1]
Case Is = 2
Value = [ACTUAL2]
Case Is = 3
Value = [ACTUAL3]
Case Is = 4
Value = [ACTUAL4]
Case Is = 5
Value = [ACTUAL5]
Case Is = 6
Value = [ACTUAL6]
Case Is = 7
Value = [ACTUAL7]
Case Is = 8
Value = [ACTUAL8]
Case Is = 9
Value = [ACTUAL9]
Case Is = 10
Value = [ACTUAL10]
Case Is = 11
Value = [ACTUAL11]
Case Is = 12
Value = [ACTUAL12]
End Select
MonthActual = Value
End Function
------------
Here's the function call from the query:
Actual: MonthActual(MonthIn)
Thank you!
-Clint Marshall