i got a query and a form, On the form it as the year which i need to display, it is in a text box.[Forms]![switchboardforH&S]![txtyears] that is where it is.
i need the crosstab query to only display this year results
this is the SQL of the query this work fine
but if i use this, which is the one that take the year value from the text box on the form it does not work
the error msg I get is "The Microsoft Jet database engine does not recognize '[Forms]![switchboardforH&S]![txtyears]' as a valid field name or expression. (Error 3070)
How can i fix this problem
thanks a lot
i need the crosstab query to only display this year results
this is the SQL of the query this work fine
Code:
TRANSFORM Count(tblAccident.[Date/Time of Accident]) AS [CountOfDate/Time of Accident]
SELECT tblDepartment.Department
FROM (tblDepartment INNER JOIN tblPerson ON tblDepartment.Department = tblPerson.Department) INNER JOIN tblAccident ON tblPerson.ID = tblAccident.PersonID
WHERE ((((Format([Date/Time of Accident],"yyyy")))="2004"))
GROUP BY tblDepartment.Department
PIVOT Format([Date/Time of Accident],"mmmm");
but if i use this, which is the one that take the year value from the text box on the form it does not work
Code:
TRANSFORM Count(tblAccident.[Date/Time of Accident]) AS [CountOfDate/Time of Accident]
SELECT tblDepartment.Department
FROM (tblDepartment INNER JOIN tblPerson ON tblDepartment.Department = tblPerson.Department) INNER JOIN tblAccident ON tblPerson.ID = tblAccident.PersonID
WHERE ((((Format([Date/Time of Accident],"yyyy")))=[Forms]![switchboardforH&S]![txtyears]))
GROUP BY tblDepartment.Department
PIVOT Format([Date/Time of Accident],"mmmm");
the error msg I get is "The Microsoft Jet database engine does not recognize '[Forms]![switchboardforH&S]![txtyears]' as a valid field name or expression. (Error 3070)
How can i fix this problem
thanks a lot