Undefined Function in Expression

[neilshah]

I have a query in the database which has an expression 'MonthName' in it. I used that expression to convert month number from the table to the month name. It worked great in the query, but now when I am trying to create a Pivot Table in Excel from the same query it's giving me this error message - Undefined Function 'MonthName' in expression.

Any suggestions on how to make this work.

Thanks.

 

[Joe4]

From what you have described, it sounds like "MonthName" is just the name of a field you are calculating in a query. It is not a function, hence you probably can't use it as a function.

If you want to use it as a function, you would have to create it as a user-defined function in VBA.

 

[neilshah]

'MonthName' is both the name of the field and the function in the query. Here is what my expression looks like

MonthName: MonthName((Month(tbl_3YearData!Start Date)),True)

This expression takes the month number from the table and returns the month name. It works perfectly fine in Access, but when I try to create a Pivot table in Excel I get the error message - Undefined Function 'MonthName' in expression. Why??

Thanks.

 

[bat17]

Using the same name for both is probably causing the problem. What does the function do?

Peter

 

[neilshah]

I tried changing the name too, but it did not work. The function takes the date, for example 3/19/04, and returns a month i.e. 3. Then it shows the month name i.e. March. That is the final output.

Thanks.

 

[bat17]

You could try doing it directly in the query with
NewMonth: Format([tbl_3YearData!Start Date],"mmmm")

HTH

Peter

 

[neilshah]

Thanks a lot. That works great!!!