Undefined function

[Gus Chuch]

Can any one help me

Why do I get an “Undefined function ‘FORMAT†in expressionâ€
When I use the following SQL in access 2003, it worked just fine with old
access 2000. Dam upgrades do I need to download something for the upgrade?

TRANSFORM Avg(Route_Income.Gross) AS AvgOfGross
SELECT Route_Income.LocationID, Avg(Route_Income.Gross) AS [Total Of Gross]
FROM Route_Income
GROUP BY Route_Income.LocationID
PIVOT Format([Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[OfficeDev18 via AccessMonster.com]

Hi, Gus,

"Date" is a reserved word in Access. Change the name in the field (MyDate,
for example) and fix the SQL. I bet it works.

Sam

Can any one help me

Why do I get an “Undefined function ‘FORMAT” in expression”
When I use the following SQL in access 2003, it worked just fine with old
access 2000. Dam upgrades do I need to download something for the upgrade?

TRANSFORM Avg(Route_Income.Gross) AS AvgOfGross
SELECT Route_Income.LocationID, Avg(Route_Income.Gross) AS [Total Of Gross]
FROM Route_Income
GROUP BY Route_Income.LocationID
PIVOT Format([Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[Gus Chuch]

I know Date is a Reserved word thats how this PIVOT Format works.
Like I said before it works fine in 2000 but not in 2003.
And the access Wizard did the Cross-tab Query.

--
thank You

Hi, Gus,

"Date" is a reserved word in Access. Change the name in the field (MyDate,
for example) and fix the SQL. I bet it works.

Sam

Can any one help me

Why do I get an “Undefined function ‘FORMAT†in expressionâ€
When I use the following SQL in access 2003, it worked just fine with old
access 2000. Dam upgrades do I need to download something for the upgrade?

TRANSFORM Avg(Route_Income.Gross) AS AvgOfGross
SELECT Route_Income.LocationID, Avg(Route_Income.Gross) AS [Total Of Gross]
FROM Route_Income
GROUP BY Route_Income.LocationID
PIVOT Format([Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[Douglas J. Steele]

Your References collection is probably messed up.

References problems can be caused by differences in either the location or
file version of certain files between the machine where the application was
developed, and where it's being run (or the file missing completely from the
target machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

 

[Gus Chuch]

That did it, I was missing a: microsoft calendar control 9.0.

But now I seem to missing some string functions (LEFT()) is one of them, am
I missing a .DLL library?
--
thank You

Your References collection is probably messed up.

References problems can be caused by differences in either the location or
file version of certain files between the machine where the application was
developed, and where it's being run (or the file missing completely from the
target machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Can any one help me

Why do I get an "Undefined function 'FORMAT" in expression"
When I use the following SQL in access 2003, it worked just fine with old
access 2000. Dam upgrades do I need to download something for the upgrade?

TRANSFORM Avg(Route_Income.Gross) AS AvgOfGross
SELECT Route_Income.LocationID, Avg(Route_Income.Gross) AS [Total Of
Gross]
FROM Route_Income
GROUP BY Route_Income.LocationID
PIVOT Format([Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[Douglas J. Steele]

What references do you have selected?

Did you adding an additional reference, backing out, then going back in and
removing it suggestion?

References are like apples: one bad one can spoil the whole bunch. Access
goes looking in the referenced files for functions, and if it comes to a bad
reference, it essentially gives up, even though the reference to the library
that contains the specific function may be fine.

If you can't fix the references, you might be able to get it to work using
VBA.Left

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

That did it, I was missing a: microsoft calendar control 9.0.

But now I seem to missing some string functions (LEFT()) is one of them,
am
I missing a .DLL library?
--
thank You

Your References collection is probably messed up.

References problems can be caused by differences in either the location
or
file version of certain files between the machine where the application
was
developed, and where it's being run (or the file missing completely from
the
target machine). Such differences are common when new software is
installed.

On the machine(s) where it's not working, open any code module (or open
the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar.
Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them,
unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back
out
of the dialog, then go back in and unselect the reference you just added.
If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back
out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Can any one help me

Why do I get an "Undefined function 'FORMAT" in expression"
When I use the following SQL in access 2003, it worked just fine with
old
access 2000. Dam upgrades do I need to download something for the
upgrade?

TRANSFORM Avg(Route_Income.Gross) AS AvgOfGross
SELECT Route_Income.LocationID, Avg(Route_Income.Gross) AS [Total Of
Gross]
FROM Route_Income
GROUP BY Route_Income.LocationID
PIVOT Format([Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[Gus Chuch]

I spoke to soon.
I am using VBA, but the missing references I had also fixed my string problem.
It kind of makes sense because I started with a FORMAT problem.
Any way, thanks for the help.

--
thank You

What references do you have selected?

Did you adding an additional reference, backing out, then going back in and
removing it suggestion?

References are like apples: one bad one can spoil the whole bunch. Access
goes looking in the referenced files for functions, and if it comes to a bad
reference, it essentially gives up, even though the reference to the library
that contains the specific function may be fine.

If you can't fix the references, you might be able to get it to work using
VBA.Left

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

That did it, I was missing a: microsoft calendar control 9.0.

But now I seem to missing some string functions (LEFT()) is one of them,
am
I missing a .DLL library?
--
thank You

Your References collection is probably messed up.

References problems can be caused by differences in either the location
or
file version of certain files between the machine where the application
was
developed, and where it's being run (or the file missing completely from
the
target machine). Such differences are common when new software is
installed.

On the machine(s) where it's not working, open any code module (or open
the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar.
Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them,
unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back
out
of the dialog, then go back in and unselect the reference you just added.
If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back
out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Can any one help me

Why do I get an "Undefined function 'FORMAT" in expression"
When I use the following SQL in access 2003, it worked just fine with
old
access 2000. Dam upgrades do I need to download something for the
upgrade?

TRANSFORM Avg(Route_Income.Gross) AS AvgOfGross
SELECT Route_Income.LocationID, Avg(Route_Income.Gross) AS [Total Of
Gross]
FROM Route_Income
GROUP BY Route_Income.LocationID
PIVOT Format([Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");