Error "The Microsoft Jet database engine does not recognize '[Forms]![Menu]![StartDate]' as a valid field name or expression."
Posted on 2006-10-19
I've created a report in MS access. I have a form that has two values that are [StartDate] and [EndDate]. Now, these work perfectly fine for one of my reports. But the other report it does not work for. In these reports, there are graphs. The Queries behind these graphs are as follows:
The one that works:
SELECT Query1.[Error Catagory], Sum(Query1.[CountOfError Catagory]) AS [SumOfCountOfError Catagory]
WHERE (((Query1.Factory)="Fast Print"))
GROUP BY Query1.[Error Catagory]
ORDER BY Sum(Query1.[CountOfError Catagory]) DESC;
The one that does not work:
TRANSFORM Sum(Query2.[CountOfError Catagory]) AS [SumOfCountOfError Catagory]
WHERE (((Query2.[Week/Year]) Between [Forms]![Menu]![StartDate] And [Forms]![Menu]![EndDate]) AND ((Query2.Factory)="Cheer time"))
GROUP BY Query2.[Week/Year]
PIVOT Query2.[Error Catagory];
Now, in the first example, Query1 contains a filter which filters based on those two dates. In hte second example, the filter used to be contained in Query2, but I moved it directly into the query for the chart in order to test if this was the problem. It does not work whether it is in query2 or in the chart query. Could you please offer some advice as to why it would work for one query and not for the other? The second one is a crosstab. Does this make the difference? Thanks.