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,724 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17769516
silly question, but the forms are open right?
0
 

Author Comment

by:loscomp
ID: 17769532
yes
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 125 total points
ID: 17770982
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 65

Expert Comment

by:rockiroads
ID: 17772202
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
ID: 17773447
defining the parameter type worked... thanks jefftwilley!
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17773769
Welcome!
J
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17774502
Nice one Jeff
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

626 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