-----Original Message-----
Sev,
Now we're getting somewhere. No need for a separate query by month, a single
query will do it. I'll assume the following names, and you'll have to
substitute the real ones:
Form name: Form1
Add two combo boxes: cboMonth, cboYear
Type the year values like:
2002, 2003, 2004 etc. in the rowsource property of cboYear (having set its
rowsource type to Value List)
For cboMonth: make a lookup table (called, say, tblMonths) with two fields,
like:
Mnth# MnthName
1 January
2 February
....
....
12 December
Make cboMonth 's rowsource the table, selecting both columns (fields), set
the Column Count property to 2, and the Column width property to something
like 0;3 so the first column (month number) does not show in the drop-down
list. Leave the Bound column property to the default 1 (so the combo "shows"
the month name, but returns the number!).
Now, make a query on Table1 (to base the graph on) like you did for a given
month, but instead of typing in criteria for the date like you did, do the
following:
Add two calculated fields:
fltrMonth: Month([Day])
fltrYear: Year([Day])
and type in these criteria respectively:
Forms![Form1]!cboMonth
Forms![Form1]!cboYear
So, the query reads the selected Month and Year from the combos on the form
everytime you run it! All you need is to have the form open and valid
selections in the combos. You can put the combos on the same form that hosts
the graph, and have the graph display the new data immediately as you change
the selection in the combos. Just be careful to use the right form and
control names in the expressions.
Note: Day is also an Access reserved keyword, so it would be advisable to
change your field name to something different.
HTH,
Nikos
Thanks for your reply.
Perhaps my ambition is to be great, but...
Table1:
Param, Text, 50
Day, Medium Date, dd-mmm-yy
Value, Single, Standard,2
In the limit what I intend it is to have a query to choose
Month to "feed" the graph in one form to present graphs
with the 30 days of the Month choosed MonthYear.
One more time, many thanks.
Sev
-----Original Message-----
Sev,
There is definitely a solution, if you care to explain! What is your table
like, and what are you trying to achieve? Where do you have "dd-mmm-yy", in
the table or as column headers in the crosstab?
Nikos
Nikos,
Thanks for your solution, but, lamently I have "dd-mmm- yy".
It is more complicated...
Sev
-----Original Message-----
Sev,
If you want monthly columns in your crosstab, then use a calculated field in
your query like:
Period: Format([fldDate]), "yyyy-mm")
It will work beautifully as Column Header in a crosstab, and sort correctly.
Note: in the above example I have assumed your date
field
to be fldDate. I
understand your field is actually named Date; it would
be
advisable to
change the field name to something else, as Date is a reserved keyword in
Access, and you may eventually run into problems in your design as a result
of the confusion (yes, Access can be confused under similar conditions).
HTH,
Nikos
Thanks for your replay.
I need to have Crosstab queries to calc values and I think
isn't possible to filter the Date.
Therefore, I have one query for month and I have in Date
Column:
Between (#01/02/2001#) And (#28/02/2001#)
I don't know solution for this...
Thanks.
Sev
-----Original Message-----
Sev,
To begin with, it looks like you have started off with a
poor design; a
separate queruy for each month shouldn't be necessary,
you should have one
query with a month filter.
Now, getting to the particular question: once you have
changed your design
to a single query, "feed" the graph from it, and make the
month criterion in
the query read directly from the combo on the form
(Forms![FormName]![ComboName]), and the job is done.
HTH,
Nikos
message
I have monthly data in queries. One query to each month.
In form, trough combobox, I need to choose the monthly
query to "nurture" the graph in subform.
I would like help, because I don´t know the best way to do
this.
(And I never saw any sample)
Any idea is welcome.
Sev
.
.
.
.
