undefined function '[function]' in expression

[sehmke]

Hello all,

I don't know if this should be in the Access or the Excel forum, but I'll try my luck here.

I have an Access Database in which I made some custom functions (for example "weeknum(date)", which formats a date as yyyyww (year + week).

I have a query in Access which uses this function with no problems.

Now I want to make a pivot table in Excel based on this Access query, using Data - Pivot Table - External Data Source (and Get Data). That way I hope to keep the pivot table in sync with the database query.

For some reason Excel can't link to the Access query containing the custom function. I get an error message: undefined function 'weeknum' in expression. How do I solve this?

Thanks for any help. Greetings from sunny Belgium.
S.

 

[NoMAd]

try putting it as a macro..

 

[sehmke]

Still not clear to me

try putting what as a macro where???

If I use a built-in function in my query (eg. Left(fieldname;2)), it works just fine.

But custom functions (written in VBA) are a hastle to rewrite in the Expression Builder... Some of them are really complex.

And I don't want to use the Pivot Table view of the query in Access. It HAS to be in Excel...

How do I solve this??

Greetings,
S.