undefined function "format" in expression

[stuffit]

I have a query I am trying to run within a chart in a report with Access 2007.

The expression I am trying to run is Expr1: format([scdate], "mmm") where I have a date field and am trying to get it to show the month on the chart on the Y axis. The only expression that I can get to work is expr1: month([scdate]) which gives me the month number. The table format is short date and the date in the table shows as follows 01/01/2012 11:11:11 AM.

I have tried numerous combinations with "format" in the expression and get the same error. I have tried renaming the "scdate" column in the table that I am pulling the information from with no luck.

Any thoughts would be appreciated.
sw

 

[accessAnswer]

you might be missing a reference ( library) or have a broken reference
to the library that has format function.

Enter
?Format(Date(), "mm/'dd/yyyy")
in the Immediate window does it work or do you get an error?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
visit msaccess2010.com ............for more help/ tutorials/ bugs fix

 

[stuffit]

Thanks for the reply so fast.

When I try this expression ?Format(Date(), "mm/'dd/yyyy")
I get an error message that reads "the expression you entered contains an invalad syntax".

sw

 

[ChiOscar]

If you only want to get the short date try: Format(Date(), "mm/dd/yyyy")

If you only want to get the month try: Format(Date(), "mm")

This should work.

 

[stuffit]

Thanks ChiOscar
I still get the same error message "undefined function "format" in expression" with both of those expressions.

sw

 

[boblarson]

Make sure that you don't have any module, table, query, form, report, macro, control, or procedure named Format. Also check to see that your VBA project name didn't accidentally get named Format.

See the screenshot below of where to check for the VBA project name:

 

[stuffit]

boblarson
It's not the project name is "productivity".
Thanks
sw

 

[boblarson]

And if you don't have any objects named format, then you may need to run a Decompile. And if that doesn't help, just import everything into a brand new database shell. (import not copy and paste)

 

[stuffit]

Thanks bob
I am out of time today but will try it tomorrow. Thanks for the ideas and the quick response.
sw

 

[accessAnswer]

Hey,

Ok. Can you try to run in VBA immediate Window:

for each r in references : debug.Print r.name, r.fullpath : next r

Lets us know what libraries show up:

Standard thing is :
C:\Program Files\Common Files\Microsoft Shared\vba\vba6\vbe6.dll
C:\Program Files\Microsoft Office\Office12\msacc.olb
C:\Program Files\Microsoft Office\Office12\acedao.dll
C:\Program Files\Common Files\System\ADO\msado25.tlb

Let us know if something is missing.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
visit msaccess2010.com for More tips/ bugs fixes/ answers and tutorials

 

[boblarson]

Hey,

Ok. Can you try to run in VBA immediate Window:

for each r in references : debug.Print r.name, r.fullpath : next r

Lets us know what libraries show up:

Standard thing is :
C:\Program Files\Common Files\Microsoft Shared\vba\vba6\vbe6.dll
C:\Program Files\Microsoft Office\Office12\msacc.olb
C:\Program Files\Microsoft Office\Office12\acedao.dll
C:\Program Files\Common Files\System\ADO\msado25.tlb

Let us know if something is missing.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
visit msaccess2010.com for More tips/ bugs fixes/ answers and tutorials

Just wanted to say thank you for teaching me something I did not know how to do before. I didn't know you could basically run a multi-step procedure in the Immediate Window and now I know how to do it thanks to your post. I learn something new about Access every day it seems and I've been doing this for 15 years.

 

[stuffit]

accessanswer
This is what I get when I run the vba in the imediate window.

VBA C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
Access C:\Program Files\Microsoft Office\Office12\MSACC.OLB
stdole C:\WINDOWS\system32\stdole2.tlb
ADODB C:\Program Files\Common Files\System\ado\msado21.tlb
MSComCtl2 C:\WINDOWS\system32\Mscomct2.ocx
MyLib C:\Documents and Settings\sweniger\My Documents\productivity\Earnies Lib.mda

Any ideas on what I need to do. I am not an expert so laymans terms please.
Thanks
sw

 

[vbaInet]

Just wanted to say thank you for teaching me something I did not know how to do before. I didn't know you could basically run a multi-step procedure in the Immediate Window and now I know how to do it thanks to your post. I learn something new about Access every day it seems and I've been doing this for 15 years.

Yep, I do that often. Very useful when I'm debugging dictionaries or checking values of arrays.

 

[stuffit]

I got it fixed. I had some bad code in the following library.
MyLib C:\Documents and Settings\sweniger\My Documents\productivity\Earnies Lib.mda

I deleted it and everything is working fine now.

Thanks for all your ideas and help.

This site is the best.
sw

 

[accessAnswer]

Wow,

So it was in in .mda created library. Interesting.
Great that you figured it out!
All the best!

 

[stuffit]

accessanswer
One problem solved created another. Now that the expression works and puts the proper information in my crosstab query the dates do not show up in my graph.

For instance the dates now showing up in the crosstab are like this: Nov/11, Dec/11, Jan/12, Feb/12 and so on.

But in my graph they are not recognizing the fiscal year 11 and it places those months in the graph as 12 so Nov/11 shows up in the graph as Nov/12.

Any thoughts on that?
sw

 

[stuffit]

I got it. I just had to format the Y axis on my graph.
Thanks everyone.
sw