Simple Graph Dilemna

[sgfromny]

I have a query that has three fields
Date, Catagory, Cost

The date is pulled from a form that gets a beginning date and end date. The query in the date field criteria is:
Between [Forms]![frmChartSelector]![txtBeginDate] And [Forms]![frmChartSelector]![txtEndDate]

When I run the query the data looks fine.

Im using the chart wizard to chart by catagory, total sales per month.

I get an error when executing the chart:
The Microsoft Jet Database does not recognize '[Forms]![frmChartSelector]![txtBeginDate]' as a valid field name or expression.

If I replace the query with the dates directly, it works fine.

Any Idea why the dates pulled from the fields on a form dont work?

Thanks In Advance

 

[dhookom]

The chart row source is probably a "crosstab" query. This means that you must enter the query parameters. While in the design view of the Row Source property, select Query|Parameters and enter:
[Forms]![frmChartSelector]![txtBeginDate] Date/Time
Do the same if you also have an end date.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sgfromny]

That doesnt seem to work. I get a Parameter Syntax error in the chart row source properties, I get an invalid bracketing error in the actual query properties.

 

[dhookom]

Paste the SQL from your Row Source into a reply.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sgfromny]

Im still having trouble with this problem,

Here is the sql from the chart:
TRANSFORM Sum([qry-SalesByCat].Cost) AS SumOfCost
SELECT (Format([Became Job Date],"mmm"" '""yy")) AS Expr1
FROM [qry-SalesByCat]
GROUP BY (Year([Became Job Date])*12+Month([Became Job Date])-1), (Format([Became Job Date],"mmm"" '""yy"))
PIVOT [qry-SalesByCat].CustCat;

Here is the sql from the query where the chart gets its data

SELECT Proposals.CustCat, [Project Info].Cost, Status.[Became Job Date]
FROM Proposals INNER JOIN ([Project Info] INNER JOIN Status ON [Project Info].[Job Number] = Status.[Job Number]) ON Proposals.[Tracking Number] = [Project Info].[Tracking Number]
GROUP BY Proposals.CustCat, [Project Info].Cost, Status.[Became Job Date]
HAVING (((Status.[Became Job Date]) Between [Forms]![frmChartSelector]![txtBeginDate] And [Forms]![frmChartSelector]![txtEndDate]));

Any Ideas would be appreciated.

 

[dhookom]

You must open either of the queries and select Query|Parameters and enter
[Forms]![frmChartSelector]![txtBeginDate] Date/Time
[Forms]![frmChartSelector]![txtEndDate] Date/Time

Invalid bracketing is a bug that appears once in a great while and you will see the parameters might have changed to:
[[Forms]![frmChartSelector]![txtBeginDate]]
If this happens, you must manually remove the leftmost and right most []s.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sgfromny]

Thanks!, I thought I tried that.. guess not.
it worked!

 

[laprog]

Hello everyone,
I 'm using Access 2000 with SQL 2000.
In my ADP project, for a report i am using a Microsoft Graph 2000 chart. The data source is a procedure which requires one parameter.
My problem is this, i cannot find where to set my parameter which would actually be coming from a combobox in a Form where you have a print button.

I have been looking for a while now...Please help me!!!!

Thanks!

Sylvie

 

[dhookom]

Please start a new thread. It would improve your chances of getting a response.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.