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,715 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
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

830 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