Undefined Function Problem

[EnS]

Hi fellas,
Ok, here's the situation. I have a number of databases that, for this particular process, connects to each other in order to submit data from two databases into one centeral database. For example, I have db1 which is the initiation point for the process, which uses qry1 in order to submit data from table1 contained within db1 to table2 contained within db2. Then using qry2, also contained in db1, the process connects to table3 in db3, extracts data that matches that which has already been sent to db2, and submits those matched records to table2 in db2.

Hope you're still with me.

This process will then be placed in other databases, such as db4, db5, etc., to extract similar data from itself and db3 and place them into the central, db2, database; where db4, db5, etc. will replace db1 in relation to the process initiation point.

Have I lost you yet.

Anyway, I used db1 to develope the process logic and it worked like a charm. Then I deployed the process to the other dbs (db4, db5, etc) and it works great. Except that now I get "Undefined Function 'getBatchNo' in expression" error when the process tries to execute qry2 in my original development site of db1. It still works in the other dbs.

Ok, so then I thought that maybe I might have changed something inadvertently. But that just simply isn't the case because I copied the mods, qrys, and macs exactly how they are in db1 to db4 and the others. Also, the function getBatchNo works fine in qry1, which is executed prior to qry2.

So, here is the SQL statement from qry1, the code that executes qry2, and the code of the function in question (getBatchNo). Also prior to any of these areas being accessed, all variables have been declared and initialized and all dbs connections have been opened. Also note that in qry2, all the "In" lists variables have been built and are correct.

qry 1 (SQL statement):
INSERT INTO
ofcs (
[List No],
Site,
[Date Prep],
GL, TC, [Doc Number],
credit, Amt, AbsValue,
FC, description, [CO Code] )
IN 'h:\0 ATM Deposit Balancing V2\DPChgOff.mdb'
SELECT
getBatchNo([DOC NO],[DATE],0) AS [List No],
"EL1" AS Site, Date() AS [Date Prep],
[11720 El Segundo1].acct AS GL,
"1" AS TC,
[11720 El Segundo1].[Doc No] AS [Doc Number],
[11720 El Segundo1].Debit AS Credit,
-[net] AS Amt, Abs([net]) AS AbsValue,
[11720 El Segundo1].fc,
"TO 29575/" & Format([fc],"0000&quot & " " & Format([date],"mmddyy&quot AS DESCRIPTION,
98 AS code
FROM
[11720 El Segundo1]
WHERE
((([11720 El Segundo1].[29575_Journal])=Yes))
WITH OWNERACCESS OPTION;

qry2 (Function):
Function send29575OffSetItems()
strSQL = "INSERT INTO OFCS
( [List No], Site,
[Date Prep], GL, FC, TC,
[Doc Number], debit, Amt,
AbsValue, [CO Code], description )
IN 'H:\0 ATM DEPOSIT BALANCING V2\DpChgOff.mdb'"
strSQL = strSQL & " SELECT
getBatchNo([ATM],[AS_OF_DATE],1)
AS [List No],
'" & conSiteCode & "' AS Site,
Date() AS [Date Prep], 29575 AS GL,
Format(getFC(ATM,0),'0000') AS FC,
2 AS TC,
STOR_FOR_DATA.ATM AS [Doc Number],
STOR_FOR_DATA.TRANS_AMNT AS debit,
[TRANS_AMNT]-0 AS Amt,
Abs(0-[TRANS_AMNT]) AS AbsValue,
98 AS code,
(Format(getItemDate([AS_OF_DATE]),'mmddyy') & ' ' & Format(getFC([ATM],1),'0000') & ' ' & Format(getAcct([ACCT_NO]),'0000000000')) AS DESCRIPTION"
strSQL = strSQL & " FROM STOR_FOR_DATA"
strSQL = strSQL & " WHERE
(((STOR_FOR_DATA.ATM) In (" & strATMList & &quot)
AND ((STOR_FOR_DATA.AS_OF_DATE)
In (SELECT IIF(FORMAT([DATE],'ddd') <> 'Fri', IIF([DATE] + 1 In (&quot; & strHolidayList & &quot, IIF(FORMAT([DATE] + 2,'ddd') LIKE 'S*', IIF([DATE] + 4 In (&quot; & strHolidayList & &quot, [DATE] + 5, [DATE] + 4),[DATE] + 2),[DATE] + 1),IIF([DATE] + 3 In (&quot; & strHolidayList & &quot,[DATE] + 4,[DATE] + 3)) FROM &quot; & conTableName & &quot; In '&quot; & dbCur.Name & &quot;' WHERE [29575_JOURNAL]=YES AND [DOC NO] = ATM)))&quot;
dbSnF.Execute strSQL
End Function

Function in Question:
Function getBatchNo(atm, dt, i)
Select Case i
Case 0
getBatchNo = conSiteID & atm & Format(dt, &quot;mmddyy&quot
Case 1
getBatchNo = conSiteID & atm & Format(getItemDate(dt), &quot;mmddyy&quot
End Select
End Function


I apologize for the length of this post, but I wanted to give you as much information about my problem as possible.

Therefore, my question is this:
Why is it that function &quot;getBatchNo&quot; works in the entire process of db4, db5, etc. and in qry1 of db1 but not in qry2 in db1? Any suggestions, I have been fighting with this for two days and it's killing me.

Also, for the record, I have tried coping the mods, qrys, and macs from db4, db5, and the others back into db1 with no success. The error persists.

Thanks for any insight that you may be able to provide, and again, I apologize for the length.

ERM

 

[KenReay]

Hi

SELECT
getBatchNo([DOC NO],[DATE],0) AS [List No],

so getBatchNo must be a user function declared in one or all of your myriad datbases, except db1

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[EnS]

KenReay, thanks for the prompt reply.

You are correct, getBatchNo is a user function, however, it is defined in all dbs, including db1.

ERM

 

[EnS]

My mistake, getBatchNo is declaired in all initiating dbs (db1, db4, db5, etc.) and not in db2 or 3.

ERM