Problems with creating a graph !!!

[Schaap]

Hi,
I made a query out of my database. The query consist of 2 columns (BudgetYear, Expense).In the BudgetYear column there are more then one of the same budget years (like 10 times 2005 etc). Because there are more expenses in one BudgetYear!

What I wanna do is : The user fills in a start year end an end year.
For every BudgetYear between the start year and end year I wanna count the Expense, like 2005 gives a total of 1000. And that's what I wanna display in a linegraph, for every BudgetYear the total Expense of that BudgetYear.
Is this possible in Access or only in VBA (writing code) ?
Please let me know how !

And how can I arrange the X and Y axis of the graph in VBA, perhaps it's also possible to select the range of the Y-axis automatically according to the maximum possible value of the total Expense of all the budgetyears.

Thx

 

[M8KWR]

have you grouped your query, and set your expense to sum?

 

[Schaap]

M8KWR,

I did grouped the query and set the expense column to sum.
Then I get an error : 'datatype not the same as in criteria expression !'
I don't use the criteria (yet)!

 

[Schaap]

I solved the problem !\
Some fields in the dbase where empty

Thx for the info.

Is it possible to fill the where-property of a column with more then 1 value like : between StartYear and EndYear (f.e. 2000 - 2005) ?
But still don't know if it is possible to automatically set the range of the axis ?

 

[Schaap]

But I still got problems with the use of more then 1 criteria per column
This is the SQL-code I use :

PARAMETERS [Fill in start budgetyear in:] Text ( 255 ), [Fill in stop budgetyear in:] Text ( 255 );
SELECT ApplicationForm.BudgetYear, Sum(ApplicationForm.Expense) AS SomVanExpense1
FROM ApplicationForm
WHERE ((([Fill in stop budgetyear:]<=([ApplicationForm].[BudgetYear]))>=[Fill in start budgetyear in:]))
GROUP BY ApplicationForm.BudgetYear
ORDER BY ApplicationForm.BudgetYear;

But I get an empty result instead of the expense sum for the selected budgetyears ???

 

[Schaap]

I get an empty result when I fill in 2 budget years (start and stop.

The not working correct SQL-code :
WHERE ((([Fill in stop budgetyear:]>=([ApplicationForm].[BudgetYear]))>=[Fill in start budgetyear in:]))

And is it possible to fill in these 2 budget years in a form instead of the standard fill in boxes ? And what will be the label to use in VBA for that ?

 

[Schaap]

It works

SQL-code :

PARAMETERS [Fill in start budgetyear in:] Text ( 255 ), [Fill in stop budgetyear in:] Text ( 255 );
SELECT ApplicationForm.BudgetYear, Sum(ApplicationForm.Expense) AS SomVanExpense1
FROM ApplicationForm
WHERE (((ApplicationForm.BudgetYear)>=[Fill in start budgetyear in:] And (ApplicationForm.BudgetYear[Fill in stop budgetyear in)
GROUP BY ApplicationForm.BudgetYear
ORDER BY ApplicationForm.BudgetYear;