Query with UDF works in Access but gives Undefined function in expression (Err 3085) in Excel

Question

I have an Access table with a date/time field. I wanted to make a composite Key field out of the date/time field and 3 other text fields in the same format as the matching Key field in another database.

So I concatenated the 3 text fields and wrote a User-Defined-Function in a Module to output the date field as a string in the format "YYYYMMDD".

Public Function YYYYMMDD(dteDate As Date) As String
    YYYYMMDD = Format(dteDate, "YYYYMMDD")
End Function

I can then successfully run my queries in Access and it all works fine.

But when I set up some DAO code in Excel and try to run the query that works fine within Access...

db.Execute "qryMake_tblValsDailyAccount"

...Excel gives me the "Undefined function in expression. (Error 3085)" error.

To me this is a bug in Excel and/or Access, because the (Excel) client shouldn't need to know anything about the internal calculations that normally take place perfectly in the (Access) server when in isolation.

Excel should send the querydef (name with no parameters) to the server, let the server do its work then receive the answers. Why does it need to get involved with a function internal to the server?

Does anyone know a way around this?

Answer 1

Ok, I've hacked a fix to it by replacing the VBA function with a query that joins to the original data table through its primary key ValsDaily_ID

SELECT tblValsDaily.ValsDaily_ID, 
Format(Year(tblValsDaily.BusinessDate))+
Format(Month(tblValsDaily.BusinessDate),"00")+
Format(Day(tblValsDaily.BusinessDate),"00") AS YYYYMMDD
FROM tblValsDaily;

but this is really lame, and I still don't know why the VBA version doesn't work...

Answer 2

Access uses Jet, and the combination of Access and Jet understands VBA functions. DAO is a generic data access layer that doesn't understand VBA functions.

When you use DAO, you're not automating Access, merely using that bridge to get to the data.

Even though some versions of Access use DAO internally to communicate with Jet, the ability to understand VBA is programmed into Access, not DAO.

I think your workaround is the best you can do.