Using Office 2003 Pro.
I wish to use a subset of data in an AccessDB Table as the data source for
an Excel PivotTable. This is normally easy and I have done it many times.
However, in this case one of the fields in the Table has several spellings
for the same entity. I need to generate a name mapping function to
standardize this field.
This is also simple. I define a VBA routine in Access to perform the mapping.
Now the Access Query has syntax (abbreviated here) like this:

SELECT AccountQuery.TransDate, NameMapper([AccountQuery]![Memo]) AS CurName,
....FROM AccountQuery ...

In this case, NameMapper is my VBA function defined in Access. Naturally, it
gives the proper results in Access.

Any reference, in Excel, to this Access Query fails complaining that
NameMapper is unknown.

I can work around this using VBA in Excel to perform the name mapping, but
that leads me to some fairly complex code in getting the PivotTable set up
and, more
difficult, to catching the PivotTable Refresh so that I can do the right
thing.

Before I get into that, I would like to know if there is a way to get
MSQuery to accept/use the Access VBA routine as defined in the Access Query?
In other words, I want to define the query in Access, using the name mapping
function, and then have Excel able to directly use that query as the data for
a PivotTable.

An alternative would be to get MSQuery to run the name mapping function as
defined in an Excel VBA module.

Since this one case is not too vital to me, I can work around it in other
ways, however, I can forsee other cases in which a user defined function in
Access would be very useful when used in a Access query providing data to
Excel.
--
Jim Parsells