Query with UDF works in Access but gives Undefined function in expression (Err 3085) in Excel
- by ronwest
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?
