database engine does not recognize as valid field name

[Guest]

Below is my SQL for a crosstab query. In ALLSTUDY1 query I am filling in th
date from a form. When I try to run this Query I get an error run-time 3070
on the formdate. I need to be able to run acording to date so if the are
several years of data it pulls only the quarters in the date range. Can
anyone 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");

 

[Guest]

hi
try this
Format(#[Forms]![FormName]![OCC_DATE]#,"q-yy");

 

[Guest]

I tried it. It did not work anymore suggestions?

hi
try this
Format(#[Forms]![FormName]![OCC_DATE]#,"q-yy");

-----Original Message-----
Below is my SQL for a crosstab query. In ALLSTUDY1 query I am filling in th
date from a form. When I try to run this Query I get an error run-time 3070
on the formdate. I need to be able to run acording to date so if the are
several years of data it pulls only the quarters in the date range. Can
anyone 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");

.