UserDefined Function in query

[RobatSara]

I have written a function Called FiscalYear in Access that converts a date to a Fiscal Year. If I use it within an expression in an Access query everything runs fine. When I run the query via ADO to get the results to excel I get a Visual Basic error that says the expression holds an undefined function FiscalYear. Any ideas on how to solve this are more than welcome.

 

[smozgur]

FiscalYear udf resides in Access environment as a custom function and you can only access it to use in queries within that project file.

Two possible solutions:

1- Executing your code in Access environment (might not be suitable for your project if getting information from Access is just a little part of the job in Excel).

2- Suitable solution: copy your FiscalYear function into the Excel module and create your query string by using this function first then use the returned string in ADO object.

I hope it helps.
Suat

 

[RobatSara]

Hi Suat,

I have tried solution # 2 and it works. Thanks for your help.

regards,

Robin

 

[smozgur]

You're welcome, Robin.
Glad to hear it helps!

Suat