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(Curr entDate),"0" &
WeekNumber(CurrentDate)) AS YearWeek, SUM([GoodParts-Plan]) AS SumPartsPlan
FROM tblDailyProduction
GROUP BY Year(CurrentDate) & "-" &
IIf(LEN(WeekNumber(CurrentDate))=2,WeekNumber(Curr entDate),"0" &
WeekNumber(CurrentDate))
ORDER BY Year(CurrentDate) & "-" &
IIf(LEN(WeekNumber(CurrentDate))=2,WeekNumber(Curr entDate),"0" &
WeekNumber(CurrentDate));