I have a crosstab query and in the criteria pane I put [Forms]![frmRptTrafficReport]![CurrentYear]. I am using a text box on unbound form for the user to enter the year.

I get an error saying it does not recognize [Forms]![frmRptTrafficReport]![CurrentYear] as a valid field name or expression. I know it is a valid field name because the value is displayed in the immediate window. The query also works if I change it to a select query.

Is there something special about crosstab query I am missing?

Thanks

What does your crosstab query look like?


Kevin

dartcoach

Dartcoach:

What info would help you to assist me. It is a standard crosstab query that works without the parameters on the unbound form. When I reference the unbound form and populate the criteria pane I get this error not recognizing the field as valid.

If I switch from a crosstab to a select query with the criteria intact I get the expected results. The data is not displayed as I would like the crosstab to do.

I hope that helps

Stoneman,
Without seeing your form, and your crosstab query sql, it's real hard to say what the problem might be. If you can, cut and paste the sql into your reply. Also, cut and paste the sql from the query when it's just a select query.

Kevin

dartcoach

The SQL below are copied from the qiery. As I said it does not recognise )=[Forms]![frmRptTrafficReport]![CurMonth] and [Forms]![frmRptTrafficReport]![CurYear] as valid fields although they work in the select query.

I hope this assist you. Thanks this roadblock is preventing me from completing this project.


TRANSFORM Count(tblVisit.VisitID) AS CountOfVisitID
SELECT tblVisit.AdSource, IIf([Sold]=Yes,"1","0") AS SoldUnits
FROM tblVisit INNER JOIN tblVisitDetails ON tblVisit.VisitID = tblVisitDetails.VisitID
WHERE (((tblVisit.MonthOf)=[Forms]![frmRptTrafficReport]![CurMonth]) AND ((tblVisit.VisitYear)=[Forms]![frmRptTrafficReport]![CurYear]))
GROUP BY tblVisit.AdSource, IIf([Sold]=Yes,"1","0"), tblVisit.MonthOf, tblVisit.VisitYear, tblVisit.DealershipID
PIVOT tblVisitDetails.VehicleCategory;


*******************


SELECT tblVisit.AdSource, IIf([Sold]=Yes,"1","0") AS SoldUnits, tblVisitDetails.VehicleCategory, Count(tblVisit.VisitID) AS CountOfVisitID
FROM tblVisit INNER JOIN tblVisitDetails ON tblVisit.VisitID = tblVisitDetails.VisitID
WHERE (((tblVisit.MonthOf)=[Forms]![frmRptTrafficReport]![CurMonth]) AND ((tblVisit.VisitYear)=[Forms]![frmRptTrafficReport]![CurYear]))
GROUP BY tblVisit.AdSource, IIf([Sold]=Yes,"1","0"), tblVisit.MonthOf, tblVisit.VisitYear, tblVisit.DealershipID, tblVisitDetails.VehicleCategory;

Stoneman,
The sql looks fine. I'm not sure why it doesn't work. Are you using the Access query design to create these queries?

dartcoach

Yes I am using the qurery design to create the queries and I am using the saved query as the recordsource of the report

Stoneman,
This is probably too simple but - is your form open when you run the report?
If it is, and you open the report with a command button, do you close the form before you open the report?

Kevin

dartcoach

Thanks Dartcoach for trying to help but I am puzzled just like you.

The form is opened while the report is opened, in fact while I am getting the error on the report I can get the values on the form in the immediate window. That is why I am unable to understand why I am getting the error.

Any other suggestion, could this be a bug in Access 2003

Stoneman,
Do you use the form variables in the output? Crosstabs work differently because of rowheadings, columns etc. In your query design, make sure that those fields are unchecked. That way they are only used at query time for criteria - but not for output.

Kevin

dartcoach