• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2746
  • Last Modified:

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

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
0
loscomp
Asked:
loscomp
  • 3
  • 2
  • 2
1 Solution
 
rockiroadsCommented:
silly question, but the forms are open right?
0
 
loscompAuthor Commented:
yes
0
 
jefftwilleyCommented:
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
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rockiroadsCommented:
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
0
 
loscompAuthor Commented:
defining the parameter type worked... thanks jefftwilley!
0
 
jefftwilleyCommented:
Welcome!
J
0
 
rockiroadsCommented:
Nice one Jeff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now