undefined function "format" in expression (1 Viewer)

stuffit · Aug 15, 2012

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 · Aug 15, 2012

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 · Aug 15, 2012

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 · Aug 15, 2012

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 · Aug 15, 2012

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

sw

boblarson · Aug 15, 2012

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 · Aug 15, 2012

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

boblarson · Aug 15, 2012

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 · Aug 15, 2012

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

accessAnswer · Aug 15, 2012

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 · Aug 15, 2012

accessAnswer said:
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 · Aug 16, 2012

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 · Aug 16, 2012

boblarson said:
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 · Aug 16, 2012

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 · Aug 16, 2012

Wow,

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

stuffit · Aug 16, 2012

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 · Aug 16, 2012

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

undefined function "format" in expression (1 Viewer)

stuffit

New member

accessAnswer

http://msaccess2010.com

stuffit

New member

ChiOscar

Registered User.

stuffit

New member

boblarson

Smeghead

Attachments

stuffit

New member

boblarson

Smeghead

stuffit

New member

accessAnswer

http://msaccess2010.com

boblarson

Smeghead

stuffit

New member

vbaInet

AWF VIP

stuffit

New member

accessAnswer

http://msaccess2010.com

stuffit

New member

stuffit

New member

Similar threads

Users who are viewing this thread