We help IT Professionals succeed at work.

Error "The Microsoft Jet database engine does not recognize '[Forms]![Menu]![StartDate]' as a valid field name or expression."

loscomp
loscomp used Ask the Experts™
on
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]
FROM Query1
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]
SELECT Query2.[Week/Year]
FROM Query2
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.

-Jonathan
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006

Commented:
silly question, but the forms are open right?

Author

Commented:
yes
Top Expert 2006
Commented:
You can't put a parameter in a crosstab query unless you define the input. I find it easier to put the parameters in the first query and it's picked up when you run the second. With Crosstab queries, you have to define your parameter type.  So for example, if you're in design view of your first query, go ahead and put your Between [Forms]![Menu]![StartDate] And [Forms]![Menu]![EndDate] in the criteria for that field. Then go up to the gray area where you drag tables into....and right click. Select Parameters from the menu there.

Now define what your parameters are

[Forms]![Menu]![StartDate] |  Date/Time
[Forms]![Menu]![EndDate]   | Date/Time

Let me know if you have any questions.
J
Top Expert 2006

Commented:
Good point by Jeff

And as u want values taken from the form,

could u not just create a query like this, which the report is based on

TRANSFORM Sum(Query2.[CountOfError Catagory]) AS [SumOfCountOfError Catagory]
SELECT Query2.[Week/Year]
FROM Query2
WHERE Query2.Factory = "Cheer Time"
GROUP BY Query2.[Week/Year]
PIVOT Query2.[Error Catagory]


then when u open the report, pass in filters that way
e.g.


Dim sSql as String

sSql = "Query2.[Week/Year] Between " & Forms!Menu!StartDate & " AND " & Forms!Menu!EndDate

DoCmd.OpenReport "rptName", acViewPreview, , sSql


You may need to tweak your filter (sSql statement), if proper dates, u may need to wrap CDATE and/or # around the form fields

Author

Commented:
defining the parameter type worked... thanks jefftwilley!
Top Expert 2006

Commented:
Welcome!
J
Top Expert 2006

Commented:
Nice one Jeff