Crosstab query (undefined function "format" in expression)

[Guest]

I am developing training material to teach how to use crosstab query wizard.
My example is to show how many employees were hired each year for each
department. I chose department name for row headings and "Hire date" for
column headings and selected "year" for the interval and directed it to count
employee ID numbers in each department per month. When I complete the wizard,
I get a message box saying that I have an undefined function "format" in
expression.

My expression is Format([HireDate],"yyyy")

My SQL statement is:
TRANSFORM Count(tblEmployees.EmployeeID) AS CountOfEmployeeID
SELECT tblEmployees.DepartmentName, Count(tblEmployees.EmployeeID) AS [Total
Of EmployeeID]
FROM tblEmployees
GROUP BY tblEmployees.DepartmentName
PIVOT Format([HireDate],"yyyy");

What is my problem? This is driving me nuts! Thanks for any help you can
offer.

 

[Michel Walsh]

Maybe a missing reference (even one that has nothing to do with the VBA
function Format) ? Have you see if one of the checked reference (in VBE,
under Tools | References...) has the word MISSING as first word for its
description?


Vanderghast, Access MVP

 

[Guest]

Create a query like below and name it something like qryEmployeesPreXtab:

SELECT tblEmployees.DepartmentName,
tblEmployees.EmployeeID,
Format([HireDate],"yyyy")
FROM tblEmployees;

Actually make that:

SELECT tblEmployees.DepartmentName,
tblEmployees.EmployeeID,
Year([HireDate]) as HireDateYear
FROM tblEmployees;

Form returns a string whereas Year returns a number. Might make a difference
if you need to sort on the year.

Then change tblEmployees to qryEmployeesPreXtab and HireDate to HireDateYear
so that the crosstab is now based on the first query.

It's almost always better to do things like parameters, criteria, and
changing/formatting data in a pre-query before the crosstab.

 

[Guest]

OK, thanks so much. Darn shame that the wizard doesn't work. More explaining
for my trainees, rather than just showing them how to use a wizard. But I
really appreciate your help. YOU ROCK!!

Create a query like below and name it something like qryEmployeesPreXtab:

SELECT tblEmployees.DepartmentName,
tblEmployees.EmployeeID,
Format([HireDate],"yyyy")
FROM tblEmployees;

Actually make that:

SELECT tblEmployees.DepartmentName,
tblEmployees.EmployeeID,
Year([HireDate]) as HireDateYear
FROM tblEmployees;

Form returns a string whereas Year returns a number. Might make a difference
if you need to sort on the year.

Then change tblEmployees to qryEmployeesPreXtab and HireDate to HireDateYear
so that the crosstab is now based on the first query.

It's almost always better to do things like parameters, criteria, and
changing/formatting data in a pre-query before the crosstab.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am developing training material to teach how to use crosstab query wizard.
My example is to show how many employees were hired each year for each
department. I chose department name for row headings and "Hire date" for
column headings and selected "year" for the interval and directed it to count
employee ID numbers in each department per month. When I complete the wizard,
I get a message box saying that I have an undefined function "format" in
expression.

My expression is Format([HireDate],"yyyy")

My SQL statement is:
TRANSFORM Count(tblEmployees.EmployeeID) AS CountOfEmployeeID
SELECT tblEmployees.DepartmentName, Count(tblEmployees.EmployeeID) AS [Total
Of EmployeeID]
FROM tblEmployees
GROUP BY tblEmployees.DepartmentName
PIVOT Format([HireDate],"yyyy");

What is my problem? This is driving me nuts! Thanks for any help you can
offer.