Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

The Microsoft Jet Engine does not recognize Forms!categoryreport!cbcategory as a valid field name or expression.

Posted on 2007-08-09
9
Medium Priority
?
349 Views
Last Modified: 2010-05-19
Hello I get the following error when trying to run a report.

The Microsoft Jet Engine does not recognize Forms!categoryreport!cbcategory as a valid field name or expression.
The report is a line chart with the following row source :

TRANSFORM Count([Category_ID]) AS [CountOfCategory_ID] SELECT [Category_ID] FROM [categoryquery]   GROUP BY [Category_ID] PIVOT [Type_Observation];

The below is the SQL for the categoryquery which contains the data that I need on the report:

SELECT SOC.Category_ID, [Date]-Weekday([Date])+2 AS Expr1, Typeofobs.Type_Observation
FROM Typeofobs INNER JOIN (Category INNER JOIN SOC ON Category.Category_ID = SOC.Category_ID) ON Typeofobs.Type_Observation_ID = SOC.Type_Observation
WHERE ((([SOC.Category_ID]=[Forms]![categoryreport]![cbCategory])=True) AND (([Date] Between [Forms]![categoryreport]![tbBegindate] And [Forms]![categoryreport]![tbEnddate] Or [Forms]![categoryreport]![tbBegindate] Is Null)=True));

If I create a chart report with the row source as defined below the chart is shown and there are no errors.

SELECT categoryquery.Expr1, Count(categoryquery.Category_ID) AS CountOfCategory_ID FROM categoryquery GROUP BY categoryquery.Expr1;

The criteria of the report is entered via a form that has a combo box to select the category the chart is required for and two text boxes to enter the date range.
The Typeofobs table contains two values namely negative and positive.
So basically I want the chart to have two lines&one representing the positive observations made and the other representing the negative observations which are filtered for a certain date range. If the date is not entered then it shows the chart for all records for the selected category.

I am completely new to access and am learning as I go along so any help would be greatly appreciated as this is driving me potty.

Thanks.
D
0
Comment
Question by:drgorsia
  • 4
  • 3
  • 2
9 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 19662653
Double check your typing on [Forms]![categoryreport]![cbCategory]
Make sure that categoryreport is the correct form name and that cbCategory is the correct name of the combo box on that form.
0
 
LVL 58

Accepted Solution

by:
harfang earned 500 total points
ID: 19662754
This is a classical problem with cross-tab queries.

Most query types allow you to use implicit parameters, as in:

SELECT * FROM tblSomeTable
WHERE datEntered > [Please enter a date:]

Cross-tabs do not. Due to the generation of new column names, all parameters need to be resolved before the query even starts. This means you cannot use implicit parameters, only explicit parameters. The example above using explicit parameters would be:

PARAMETERS [Please enter a date:] Date;
SELECT * FROM tblSomeTable
WHERE datEntered > [Please enter a date:]

(Notice the semi-colon as separator.) Applied to your query:

PARAMETERS
    [Forms]![categoryreport]![cbCategory] Long,
    [Forms]![categoryreport]![tbBegindate] Date,
    [Forms]![categoryreport]![tbEnddate] Date;
TRANSFORM Count([Category_ID]) AS [CountOfCategory_ID]
SELECT [Category_ID] FROM [categoryquery]
GROUP BY [Category_ID]
PIVOT [Type_Observation];

Note: in query design, this is the menu (Query | Parameters...)

Cheers!
(°v°)
0
 

Author Comment

by:drgorsia
ID: 19662757
i have checked that and everything is ok....
i forgot to mention above...when i try to run the report from the form...the chart is basically empty...no data whatsoever....
when i try opening the report via the reports tab in access the error is shown.
but for the other report chart that works....if i was to double click on that report under the reports tab then i get pop up boxes asking me to enter the criteria for category and the begin date and end date which tells me that this report is working fine and on entering the criteria i also get a proper chart with the relevant data.
sorry if the above is a bit hard to understand, since i am new to access....explainin everythin in words is a bit difficult as well...i think its just one of those days!!!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:drgorsia
ID: 19662776
harfang,

I will try what you have advised and get back to you in a while.....
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19662818
> i get pop up boxes ...
Are you ensuring that the form categoryreport is open at the time this runs?
0
 
LVL 58

Expert Comment

by:harfang
ID: 19662833
I found the relevant links:

ACC2000: Error When Running Crosstab Query with a Parameter
http://support.microsoft.com/kb/209778

How to create a parameter query in Access 2000
http://support.microsoft.com/kb/304353

Good luck!
(°v°)
0
 

Author Comment

by:drgorsia
ID: 19662996
well guys i have to go home now....will try playing around with it when i get home....i will let you know by tomorrow what the outcome is....thanks for all the help until now...once i have resolved i will assign the points.
thanks again to the both of you for your advice so far....greatly appreciated....i proabably will bug you more tomorrow if i cant get it to work at home.
Cheers,
D
0
 

Author Comment

by:drgorsia
ID: 19664067
harfang....your suggestion worked perfect....cant thank you enough. I'll be back again if i encounter any problems with the database i am trying to finish off..Thanks again guys....
0
 
LVL 58

Expert Comment

by:harfang
ID: 19664759
Welcome! I remember hitting that particular problem and ripping my hair out over it!
Success with your project,
(°v°)
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Implementing simple internal controls in the Microsoft Access application.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

572 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