Solved

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

Posted on 2006-10-19
7
2,708 Views
Last Modified: 2011-10-03
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
Comment
Question by:loscomp
  • 3
  • 2
  • 2
7 Comments
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
silly question, but the forms are open right?
0
 

Author Comment

by:loscomp
Comment Utility
yes
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 125 total points
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 

Author Comment

by:loscomp
Comment Utility
defining the parameter type worked... thanks jefftwilley!
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
Welcome!
J
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Nice one Jeff
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now