MonthName function in Access 2k not working 2

[CandyT]

A co-worker added the MonthName function to an Access 2000 db in a query. It works on his machine but not on mine and I cannot find out why. We both have Access 2000 on Windows 2002 OS. On my machine I get the following error message "Undefined function 'MonthName' in expression". I have the latest MDAC download as well as the latest service pack and it still does not work. Any suggestions? It has to be simple, but I CANNOT FIGURE IT OUT.

Thanks!

Candy

 

[jrbarnett]

Candy

Can you post the code for the Monthname function here please? So we can see what external references it uses (if anything).

John

 

[CandyT]

This is the code:

'Department DIM DMT_EFF_ENDDT: MonthName(Month(([DMT_EFF_ENDDT]))) & ', ' & Year([DMT_EFF_ENDDT])

It is not referecing anything external, it is simply that the functionality is not working in my version of Access an dI cannot figure out why. The "Depart DIM DMT..." are all tables references.

Thanks

Candy

 

[jrbarnett]

Candy

This looks like a column from a query which takes the result of the MonthName function passing in the Month of the field [DMT_EFF_ENDDT].

From what you say here it is likely that the expected output is something like:

July, 2003

To find the actual VBA code, somewhere in the VBA code modules in the database there will be code starting something like:

Variablename will probably change.

Public Function MonthName (variablename As Date) As String

and carries on down to
End Function

See if you can find this.

John

 

[CandyT]

This is not code in a module. It is a built-in function in Access so there is no VBA to go and look at.

Candy

 

[GoDawgs]

You're missing a reference most likely, get into code and hit tools, references. There will probably be one that says "MISSING" next to it after you run that query...if not make sure that the ones you have checked are the same as the co-worker's. Hope that helps, sorry if I'm talking well below your level.

Kevin

 

[CandyT]

godawgs:

I think you are exactly right, but we cannot figure out which reference we may be missing. The problem is that while my co-worker was out on leave for a broken leg, his hard drive was replaced because it had crashed. After re-install, he no longer has the functionality either

I am going to play around with references againa nd see if I can find it....

If anyone knows the specific reference I need, I am open to suggestions.

Thank you!!!!

 

[GoDawgs]

So it doesn't actually say "MISSING" next to any of them? That's disappointing. Yeah, it's definitely a reference, but I'm not sure which one either. Sorry...good luck.

Kevin

 

[jrbarnett]

Try this instead:

'Department DIM DMT_EFF_ENDDT: Format([DMT_EFF_ENDDT],"mmmm&quot & ', ' & Year([DMT_EFF_ENDDT])

By the way - I wasn't aware of the Monthname function. You learn something new every day.

John

 

[danvlas]

Format([DMT_EFF_ENDDT],"mmmm, yyyy&quot
will display exactly what you need

And MonthName should only be used when you have a numeric value that you want to convert to a month name, NOT when you have the full date.

MonthName(Month(YourDate)) & ", " & Year(YourDate) actually evaluates 3 functions in the same line

Format(YourDate, "mmmm, yyyy&quot evaluates only one...

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[danvlas]

Format([DMT_EFF_ENDDT],"mmmm, yyyy&quot
will display exactly what you need

And MonthName should only be used when you have a numeric value that you want to convert to a month name, NOT when you have the full date.

MonthName(Month(YourDate)) & ", " & Year(YourDate) actually evaluates 3 functions in the same line

Format(YourDate, "mmmm, yyyy&quot evaluates only one...

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[CandyT]

Daniel,
The elusive MonthName function frustrated me completely yesterday!!!

I used the Format function and voila! problem solved. Thank you so much Daniel!



John, I wasn't aware of the MonthName function either and since it is not available to me in the function menu then I wish I had never heard of it!! Thanks for all of your help.

Candy

 

[Goedland]

Don't know if anyone cares about this but I found MS KB article 225956 that addresses this issue. Based upon that I created the following function and it seems to have taken care of the problem.

Function MName(MonthN)
MName = MonthName(MonthN)
End Function

Just add it to a module and it should work fine.

Andy

 

[CandyT]

That's great! Thanks for the response!!! I will definitley use it now!!