Undefined function in expression

Question

Hi

I have an expression MonthName(Month([date])) as one of the columns in a SELECT query. I am getting below error when query is run.

This used to work fine and have only started recently. What am I missing?

Thanks

Regards

Answer 1

Open the VBA Editor, and go to Tools --> References from the toolbar menu.  Are any references labeled as MISSING?

If so, un-check them, locate and check the current version from the list, then click OK to save and close.

Also, is 'Date' the name of a column in your table, or are you trying to use "today's date"?

If it is a column name, the syntax is correct... but it is best to avoid the use of  reserved words/functions/special characters in your naming conventions (DateEntered for example would be a better choice).

If you are trying to use today's date, the syntax should be:

MonthName(Date())

---

Miriam Bizup Access MVP

Answer 2

Hi Miriam

References seem to be OK.

Date is a column name. However MonthName(Month(#22/04/2016#)) also gives the same error as a query column.

However in immediate window; 

? MonthName(Month(#2016-04-22#))

returns April.

Thanks

Regards

Answer 3

Give us a look at your expression!

MonthName is a VBA function, so it should be "found" in desktop apps.

---

peter n roth - http://PNR1.com, Maybe some useful stuff

Answer 4

Hi Peter

Below expression gives error;

SELECT MonthName(Month([date])) AS Expr1
FROM Events;

Date is a field in the Events table. This used to work fine. I would have thought that db has gone corrupt but most other functions seem to work fine. Also as mentioned above in Immediate windows MonthName works OK.

Thanks

Regards

Answer 5

Public Function MyMonthName(d As Date) As String
    MyMonthName = MonthName(Month(d))
End Function

When I use the above function in this query

SELECT Events_1.aDate, Month([aDate]) AS Expr1, MonthName([aDate]) AS Expr2, MyMonthName([aDate]) AS Expr3
FROM Events AS Events_1;

the results are

aDate	      Expr1	Expr2	Expr3
4/23/2016	4	#Func!	April

so it looks like A2010 doesn't see all of the VBA functions ...

---

peter n roth - http://PNR1.com, Maybe some useful stuff

Answer 6

Did you define your MonthName() as a public function?

Answer 7

MonthName() is a built-in function.

Answer 8

Hi Yahya,

I suggest you make a test with query below to check whether it is related with your records.

SELECT MonthName(Month(#2016-04-22#))

Also, I suggest you copy your database to other pcs to check whether this issue still exist.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 9

Can you run this code on another computer with Access installed? Seems like something is broken.

---

Best regards, George

Answer 10

Can you run this code on another computer with Access installed? Seems like something is broken.

---

Best regards, George

Hi George

Tried but same results. 

I have chosen Peter's method and it works.

Regards

Answer 11

Hi Yahya,

I am glad you have found a working way, I suggest you mark the reply from Peter as answer, and then others who run into the same issue would find the solution easily.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.