format command

[Guest]

Hello,

I have a query which has a format command in it. It works fine on the owner
of the machine who created it. But it does not work on other machines. Also
it was woriking fine for the rest of the day then it stopped working. When i
try to print he reports the error i get is - Undefined function 'format' in
the expression. I think it is a query specific problema nd does not have
anything to dod with the printing. The sql code for the query is as below

TRANSFORM First([DATA CALCULATED METRIC].DCM_Actual_Nominal) AS
FirstOfDCM_Actual_Nominal
SELECT [CALCULATED METRIC].CM_Name_ID, [CALCULATED METRIC].CM_Name, [DATA
CALCULATED METRIC].OU_Name_ID, [ORGANIZATIONAL UNIT].OU_Name, [DATA
CALCULATED METRIC].LOB_Name_ID, [LINE OF BUSINESS].LOB_Name
FROM PROGRAM INNER JOIN ((OPPORTUNITY INNER JOIN (INITIATIVE INNER JOIN
[CALCULATED METRIC] ON INITIATIVE.I_Name_ID = [CALCULATED METRIC].I_Name_ID)
ON OPPORTUNITY.O_Name_ID = INITIATIVE.O_Name_ID) INNER JOIN (([DATA
CALCULATED METRIC] INNER JOIN [LINE OF BUSINESS] ON [DATA CALCULATED
METRIC].LOB_Name_ID = [LINE OF BUSINESS].LOB_Name_ID) INNER JOIN
[ORGANIZATIONAL UNIT] ON [DATA CALCULATED METRIC].OU_Name_ID =
[ORGANIZATIONAL UNIT].OU_Name_ID) ON [CALCULATED METRIC].CM_Name_ID = [DATA
CALCULATED METRIC].CM_Name_ID) ON PROGRAM.P_Name = OPPORTUNITY.P_Name
WHERE ((([DATA CALCULATED METRIC].DCM_Date) Between
DateAdd("m",-6,[forms]![Output print selection]![combo24]) And
[forms]![Output print selection]![combo24]) AND (([CALCULATED
METRIC].CM_Value_type)=2) AND ((PROGRAM.P_Name)=[forms]![Output print
selection]![p_name]))
GROUP BY [CALCULATED METRIC].CM_Name_ID, [CALCULATED METRIC].CM_Name, [DATA
CALCULATED METRIC].OU_Name_ID, [ORGANIZATIONAL UNIT].OU_Name, [DATA
CALCULATED METRIC].LOB_Name_ID, [LINE OF BUSINESS].LOB_Name
PIVOT Format([DCM_Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Below is in the design view of the query.
Expr1: Format([DCM_Date],"mmm")
Group By
Column Heading
I would appreciate i someone can help me on the issue.

Thank you.

 

[Roger Carlson]

I don't think it has anything to do with the format function. This error
message is highly misleading. Check your References. In any code window,
go to Tools > References and make sure one of them doesn't have the word
MISSING after it. If it does, uncheck it and close the window. Most times,
Access will find the missing reference on it's own. For more information on
this, check here:
http://members.iinet.net.au/~allenbrowne/ser-38.html

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hello,

I have a query which has a format command in it. It works fine on the owner
of the machine who created it. But it does not work on other machines. Also
it was woriking fine for the rest of the day then it stopped working. When i
try to print he reports the error i get is - Undefined function 'format' in
the expression. I think it is a query specific problema nd does not have
anything to dod with the printing. The sql code for the query is as below

TRANSFORM First([DATA CALCULATED METRIC].DCM_Actual_Nominal) AS
FirstOfDCM_Actual_Nominal
SELECT [CALCULATED METRIC].CM_Name_ID, [CALCULATED METRIC].CM_Name, [DATA
CALCULATED METRIC].OU_Name_ID, [ORGANIZATIONAL UNIT].OU_Name, [DATA
CALCULATED METRIC].LOB_Name_ID, [LINE OF BUSINESS].LOB_Name
FROM PROGRAM INNER JOIN ((OPPORTUNITY INNER JOIN (INITIATIVE INNER JOIN
[CALCULATED METRIC] ON INITIATIVE.I_Name_ID = [CALCULATED METRIC].I_Name_ID)
ON OPPORTUNITY.O_Name_ID = INITIATIVE.O_Name_ID) INNER JOIN (([DATA
CALCULATED METRIC] INNER JOIN [LINE OF BUSINESS] ON [DATA CALCULATED
METRIC].LOB_Name_ID = [LINE OF BUSINESS].LOB_Name_ID) INNER JOIN
[ORGANIZATIONAL UNIT] ON [DATA CALCULATED METRIC].OU_Name_ID =
[ORGANIZATIONAL UNIT].OU_Name_ID) ON [CALCULATED METRIC].CM_Name_ID = [DATA
CALCULATED METRIC].CM_Name_ID) ON PROGRAM.P_Name = OPPORTUNITY.P_Name
WHERE ((([DATA CALCULATED METRIC].DCM_Date) Between
DateAdd("m",-6,[forms]![Output print selection]![combo24]) And
[forms]![Output print selection]![combo24]) AND (([CALCULATED
METRIC].CM_Value_type)=2) AND ((PROGRAM.P_Name)=[forms]![Output print
selection]![p_name]))
GROUP BY [CALCULATED METRIC].CM_Name_ID, [CALCULATED METRIC].CM_Name, [DATA
CALCULATED METRIC].OU_Name_ID, [ORGANIZATIONAL UNIT].OU_Name, [DATA
CALCULATED METRIC].LOB_Name_ID, [LINE OF BUSINESS].LOB_Name
PIVOT Format([DCM_Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Below is in the design view of the query.
Expr1: Format([DCM_Date],"mmm")
Group By
Column Heading
I would appreciate i someone can help me on the issue.

Thank you.

 

[Guest]

Hello,

Thanks for your prompt reply, but if i try to run only that query it gives
me the same error, hence i am positive that it has only to do with the
queries. Also i checked that there is no missing refrence to it.

The last time we had that problem we copied the data in a new version of the
database and it worked fine.

Thank you.

I don't think it has anything to do with the format function. This error
message is highly misleading. Check your References. In any code window,
go to Tools > References and make sure one of them doesn't have the word
MISSING after it. If it does, uncheck it and close the window. Most times,
Access will find the missing reference on it's own. For more information on
this, check here:
http://members.iinet.net.au/~allenbrowne/ser-38.html

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hello,

I have a query which has a format command in it. It works fine on the owner
of the machine who created it. But it does not work on other machines. Also
it was woriking fine for the rest of the day then it stopped working. When i
try to print he reports the error i get is - Undefined function 'format' in
the expression. I think it is a query specific problema nd does not have
anything to dod with the printing. The sql code for the query is as below

TRANSFORM First([DATA CALCULATED METRIC].DCM_Actual_Nominal) AS
FirstOfDCM_Actual_Nominal
SELECT [CALCULATED METRIC].CM_Name_ID, [CALCULATED METRIC].CM_Name, [DATA
CALCULATED METRIC].OU_Name_ID, [ORGANIZATIONAL UNIT].OU_Name, [DATA
CALCULATED METRIC].LOB_Name_ID, [LINE OF BUSINESS].LOB_Name
FROM PROGRAM INNER JOIN ((OPPORTUNITY INNER JOIN (INITIATIVE INNER JOIN
[CALCULATED METRIC] ON INITIATIVE.I_Name_ID = [CALCULATED METRIC].I_Name_ID)
ON OPPORTUNITY.O_Name_ID = INITIATIVE.O_Name_ID) INNER JOIN (([DATA
CALCULATED METRIC] INNER JOIN [LINE OF BUSINESS] ON [DATA CALCULATED
METRIC].LOB_Name_ID = [LINE OF BUSINESS].LOB_Name_ID) INNER JOIN
[ORGANIZATIONAL UNIT] ON [DATA CALCULATED METRIC].OU_Name_ID =
[ORGANIZATIONAL UNIT].OU_Name_ID) ON [CALCULATED METRIC].CM_Name_ID = [DATA
CALCULATED METRIC].CM_Name_ID) ON PROGRAM.P_Name = OPPORTUNITY.P_Name
WHERE ((([DATA CALCULATED METRIC].DCM_Date) Between
DateAdd("m",-6,[forms]![Output print selection]![combo24]) And
[forms]![Output print selection]![combo24]) AND (([CALCULATED
METRIC].CM_Value_type)=2) AND ((PROGRAM.P_Name)=[forms]![Output print
selection]![p_name]))
GROUP BY [CALCULATED METRIC].CM_Name_ID, [CALCULATED METRIC].CM_Name, [DATA
CALCULATED METRIC].OU_Name_ID, [ORGANIZATIONAL UNIT].OU_Name, [DATA
CALCULATED METRIC].LOB_Name_ID, [LINE OF BUSINESS].LOB_Name
PIVOT Format([DCM_Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Below is in the design view of the query.
Expr1: Format([DCM_Date],"mmm")
Group By
Column Heading
I would appreciate i someone can help me on the issue.

Thank you.

 

[Guest]

hello,

I would appreciate a quick reply, sorry but we have to get our new version
out as early as possible.

I don't think it has anything to do with the format function. This error
message is highly misleading. Check your References. In any code window,
go to Tools > References and make sure one of them doesn't have the word
MISSING after it. If it does, uncheck it and close the window. Most times,
Access will find the missing reference on it's own. For more information on
this, check here:
http://members.iinet.net.au/~allenbrowne/ser-38.html

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hello,

I have a query which has a format command in it. It works fine on the owner
of the machine who created it. But it does not work on other machines. Also
it was woriking fine for the rest of the day then it stopped working. When i
try to print he reports the error i get is - Undefined function 'format' in
the expression. I think it is a query specific problema nd does not have
anything to dod with the printing. The sql code for the query is as below

TRANSFORM First([DATA CALCULATED METRIC].DCM_Actual_Nominal) AS
FirstOfDCM_Actual_Nominal
SELECT [CALCULATED METRIC].CM_Name_ID, [CALCULATED METRIC].CM_Name, [DATA
CALCULATED METRIC].OU_Name_ID, [ORGANIZATIONAL UNIT].OU_Name, [DATA
CALCULATED METRIC].LOB_Name_ID, [LINE OF BUSINESS].LOB_Name
FROM PROGRAM INNER JOIN ((OPPORTUNITY INNER JOIN (INITIATIVE INNER JOIN
[CALCULATED METRIC] ON INITIATIVE.I_Name_ID = [CALCULATED METRIC].I_Name_ID)
ON OPPORTUNITY.O_Name_ID = INITIATIVE.O_Name_ID) INNER JOIN (([DATA
CALCULATED METRIC] INNER JOIN [LINE OF BUSINESS] ON [DATA CALCULATED
METRIC].LOB_Name_ID = [LINE OF BUSINESS].LOB_Name_ID) INNER JOIN
[ORGANIZATIONAL UNIT] ON [DATA CALCULATED METRIC].OU_Name_ID =
[ORGANIZATIONAL UNIT].OU_Name_ID) ON [CALCULATED METRIC].CM_Name_ID = [DATA
CALCULATED METRIC].CM_Name_ID) ON PROGRAM.P_Name = OPPORTUNITY.P_Name
WHERE ((([DATA CALCULATED METRIC].DCM_Date) Between
DateAdd("m",-6,[forms]![Output print selection]![combo24]) And
[forms]![Output print selection]![combo24]) AND (([CALCULATED
METRIC].CM_Value_type)=2) AND ((PROGRAM.P_Name)=[forms]![Output print
selection]![p_name]))
GROUP BY [CALCULATED METRIC].CM_Name_ID, [CALCULATED METRIC].CM_Name, [DATA
CALCULATED METRIC].OU_Name_ID, [ORGANIZATIONAL UNIT].OU_Name, [DATA
CALCULATED METRIC].LOB_Name_ID, [LINE OF BUSINESS].LOB_Name
PIVOT Format([DCM_Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Below is in the design view of the query.
Expr1: Format([DCM_Date],"mmm")
Group By
Column Heading
I would appreciate i someone can help me on the issue.

Thank you.