Excel 2000 VBA undefined function problem

[Matt.]

Hi all!

In the code below, I am getting the error "Undefined Function Weeknumber in
the expression". The query qryManagementControlReport01 is an Access 2000
query, and there is a module in the Access database defined by connDB that
has the function WeekNumber in it. WeekNumber is also defined in my Excel
spreadsheet in the same module as this code is executed from. I read
somewhere it wasn't possible to exceute an Access module from an Excel VBA
module, and I had hoped that creating the query in Access would resolve the
need to make the call. The query in Access runs as expected. The Excel
module compiles and Saves without error.

How do I get this to work?

Any help would be greatly appreciated,

Thanks Matt.


Excel
intWeekHolder = WeekNumber(ExcelDate)
strSQL = "SELECT SumPartsPlan " _
& "FROM qryManagementControlReport01 " _
& "WHERE YearWeek = " & Year(ExcelDate) & "-" &
IIf(Len(intWeekHolder) = 1, "0" & intWeekHolder, intWeekHolder)
Set rsJobs = connDB.Execute(strSQL)
Cells(intCurRow, intCurCol + 2).Value = rsJobs("SumPartsPlan")

Access
SELECT Year(CurrentDate) & "-" &
IIf(LEN(WeekNumber(CurrentDate))=2,WeekNumber(CurrentDate),"0" &
WeekNumber(CurrentDate)) AS YearWeek, SUM([GoodParts-Plan]) AS SumPartsPlan
FROM tblDailyProduction
GROUP BY Year(CurrentDate) & "-" &
IIf(LEN(WeekNumber(CurrentDate))=2,WeekNumber(CurrentDate),"0" &
WeekNumber(CurrentDate))
ORDER BY Year(CurrentDate) & "-" &
IIf(LEN(WeekNumber(CurrentDate))=2,WeekNumber(CurrentDate),"0" &
WeekNumber(CurrentDate));

 

[Charles Williams]

Hi Matt,

The only bypass I found to this problem is to create a new field and then
loop through the recordset populating it using the function.


hth
Charles Williams
www.DecisionModels.com