Access as Excel Datasource: Undefined Function error - no missing references!

[timmychap]

OK, this is driving me a little bit mad.

I have a database in Access which I am trying use as a datasource for some excel pivot tables.

This works fine, except for queries that use a public function that I have developed (it's a a simple classification process based off a case statement with various Instr checks for specific string occurrences - see code below, although I don't think it's the issue).

I have found various apparent fixes to this issue based around missing references, or references in incorrect locations.

I have checked references, and none are marked as missing. I have checked the location of the references, and they seem to be pointing to the right location for the reference.

The database was developed in Office 2007, and I am testing on the same pc that it was developed on. It is not being shared with other users.

It has only ever had Win7 and Office2007 installed on it, so should have no issues with prior versions/old references. All recent service packs are also installed.

Completely lost where to go with this - any general hints on another area to look into would be greatly appreciated.

Cheers

Tim

Option Compare Database

Option Explicit

Public Function Retail_SubChannel(Retailer As String)

Select Case True
    Case InStr(Retailer, "a ") > 0
        ePay_SubChannel = "Type A"
    
    Case InStr(Retailer, "b ") > 0
        ePay_SubChannel = "Type B"
.
.
.
.
    Case InStr(Retailer, "z ") > 0
        ePay_SubChannel = "Type Z"
    
    Case Else
        ePay_SubChannel = "Type Other"
End Select
    
End Function

 

[Micron]

I don't understand where you tried to run the query from. My suggestion was while in Access in the VB editor, but I suspect you meant you still get the error when trying to run any query in the Access db from Excel. Also, if your function in the original post is supposed to return a value to the query, you have not set it as far as I can see.

Public Function Retail_SubChannel(Retailer As String)

Select Case True
    Case InStr(Retailer, "a ") > 0
        ePay_SubChannel = "Type A"
    
    Case InStr(Retailer, "b ") > 0
        ePay_SubChannel = "Type B"
    Case InStr(Retailer, "z ") > 0
        ePay_SubChannel = "Type Z"
    Case Else
        ePay_SubChannel = "Type Other"
End Select

Retail_SubChannel =?????

End Function

 

[TSDPE]

Thank you Micron for your intention
Seems to be somecind of environmental problem,
since as stated in my previous mail query without any UDF olso fail
I'm trying something else,
Thank's