database engine does not recognize as valid field name

[Guest]

SELECT dbo_STUDY.STUDY_ID, dbo_STUDY.STUDY_DESC, dbo_STUDY_DETAIL.PAT_ID,
dbo_STUDY_DETAIL.OCC_DATE, "Qtr " & Format([OCC_DATE],"q-yy") AS Quarters,
dbo_STUDY_DETAIL.OCC_TIME, dbo_STUDY_DETAIL.PT_IMPACT,
dbo_STUDY_DETAIL.MED_ERR_CAT_ID, dbo_STUDY_DETAIL.TYPE_INJURY_ID,
dbo_STUDY_DETAIL.COMP_SOURCE_ID, dbo_STUDY_DETAIL.MED_ID,
dbo_STUDY_DETAIL.BODY_PART_ID, dbo_STUDY_DETAIL.OCC_CAT_ID,
dbo_STUDY_DETAIL.SHIFT_ID, dbo_STUDY_DETAIL.EVENT_DESC,
dbo_STUDY_DETAIL.DEPT_ID, dbo_DEPT.DEPT_NAME
FROM ((dbo_STUDY INNER JOIN dbo_STUDY_DETAIL ON dbo_STUDY.STUDY_ID =
dbo_STUDY_DETAIL.STUDY_ID) INNER JOIN dbo_CASE_TYPE ON dbo_STUDY.STUDY_ID =
dbo_CASE_TYPE.STUDY_ID) INNER JOIN dbo_DEPT ON dbo_STUDY_DETAIL.DEPT_ID =
dbo_DEPT.DEPT_ID
WHERE (((dbo_STUDY_DETAIL.OCC_DATE) Between [Forms]![MainMenu]![StartDate]
And [Forms]![MainMenu]![End Date]));

The above is my first query it runs great. I used this query to build five
crosstab queries. None of which work with the where date. Below is one of my
queries that is not working right can anyone help me.

See how this one is using the above query, but every time I trie to run this
I get a run time error 3070 does not recognize for the date. Please help me.

TRANSFORM Count(qryALLSTUDY1.STUDY_ID) AS CountOfSTUDY_ID
SELECT qryALLSTUDY1.STUDY_DESC, qryALLSTUDY1.DEPT_NAME,
Count(qryALLSTUDY1.STUDY_ID) AS [Total Of STUDY_ID]
FROM qryALLSTUDY1
GROUP BY qryALLSTUDY1.STUDY_DESC, qryALLSTUDY1.DEPT_NAME
PIVOT "Qtr " & Format([OCC_DATE],"q-yy");

 

[Douglas J. Steele]

The name of the field in your query is Quarters: use that instead of "Qtr "
& Format([OCC_DATE],"q-yy")