"External Name Not Defined" in function called by query

[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

 

[Albert D. Kallal]

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?

You are 100% correct in both of the above conclusion. The code will have no
idea what table, or even what record you are try to deal with.

A better way would be to pass the 12 values to the code routine. You could
also pass the record id to the code and load up a reocrdset, but that will
make the function run VERY VERY slow.

Sounds like you have some very un-normalized data here. Those 12 fields
should really be one DATE field + a data field in another "related" table.
You can then use sql, and reports to generate your data. As it is now, you
will forever more be writing functions that could be avoided.

Further, with normalized data, you can find max, or min, or easily sum the
values. And, you can sum/span seveal date periods. With your current 12
fields, you have a VERY difficult data set to work with.

 

[Clint Marshall]

Good counsel, thank you!
Can you give me a little more guidance on how to best set up these tables in
a normalized manner?
Each record of the table includes:

Account#
Account Description
Budget Comment
Actual Comment
Budget1
Actual1
Budget2
Actual2
....
Budget12
Actual12

How would I set up the different tables?

Thank You!

-Clint Marshall

 

[Guest]

This may be helpful for your immediate problem. Correcting the structure of
your database will take some time. I use this in a query where I have a
similar database struture problem (inherited, I didn't do it). To use it,
create a calculated field in your query and call it like this:
ActualYTD:
Sum(IIf([Identifier]="A",calc_ytd(Month([Forms]![frmMenu]![ngcmsdate]),[jan],[feb],[mar],[apr],[may],[jun],[jul],[aug],[sep],[oct],[nov],[dec]),0))

Public Function Calc_Ytd(MoNo As Integer, January As Double, February As
Double, _
march As Double, april As Double, may As Double, june As Double, july As
Double, _
August As Double, September As Double, October As Double, November As
Double, _
December As Double) As Double
' Dave Hargis 8/04
' Calculates Year To Date Totals
'MoNo is the last month to include in the calculation
'For example, to include January through August, it should be 8

'Be Sure we Don't Have any Null Values
January = Nz(January, 0)
February = Nz(February, 0)
march = Nz(march, 0)
april = Nz(april, 0)
may = Nz(may, 0)
june = Nz(june, 0)
july = Nz(july, 0)
August = Nz(August, 0)
September = Nz(September, 0)
October = Nz(October, 0)
November = Nz(November, 0)
December = Nz(December, 0)

Select Case MoNo
Case 1
Calc_Ytd = January
Case 2
Calc_Ytd = January + February
Case 3
Calc_Ytd = January + February + march
Case 4
Calc_Ytd = January + February + march + april
Case 5
Calc_Ytd = January + February + march + april + may
Case 6
Calc_Ytd = January + February + march + april + may + june
Case 7
Calc_Ytd = January + February + march + april + may + june + july
Case 8
Calc_Ytd = January + February + march + april + may + june +
july + August
Case 9
Calc_Ytd = January + February + march + april + may + june +
july + August _
+ September
Case 10
Calc_Ytd = January + February + march + april + may + june +
july + August _
+ September + October
Case 11
Calc_Ytd = January + February + march + april + may + june +
july + August _
+ September + October + November
Case 12
Calc_Ytd = January + February + march + april + may + june +
july + August _
+ September + October + November + December

End Select


End Function

 

[Clint Marshall]

Thanks!
I got it working using your technique and am now working to normalize the
tables.

-Clint Marshall