Yes I have a couple of things that have happen over lunch that i would like
to ask you.
1) this is the row source for my chart:
SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS
[Count]
FROM [BILLING LOG]
WHERE ((([BILLING LOG].[DATE TO PRINT ROOM]) Between
[Forms]![date_frm]![srtdate] And [Forms]![date_frm]![endDate]))
GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT
ROOM])*12+Month([DATE TO PRINT ROOM])-1
ORDER BY Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1;
When i enter the dates in the form that I created and open the report to
view the chart it is missing the first month that i type in.
2.when the chart opens the pages keep going and going and going. It seems
that when you go to the next page it repeats the query over and shows the
same chart.
3. the last thing i wanted to ask you was instead of using the form to enter
the dates can i have it ask you for the dates like a perameter?
Duane Hookom said:
I'm not sure from your reply if you need more assistance. If you do, come
back with your actual Row Source etc.
--
Duane Hookom
Microsoft Access MVP
:
Thanks so much it works great. Just one more thing. I don't think that the
date group by and order by are correct. If i type in the start date on the
form as 1/1/07 and the end date of 12/31/07 it starts feb 07 it does not show
jan. I guess i do not need to create the query i just need to create the sql
in the chart.
Thanks again! you were a big help.
:
The wizards sometimes muck up your results. That's why you often need to
manually reset some properties especially Row Sources.
--
Duane Hookom
Microsoft Access MVP
:
I used that in the query and when i went to use the chart wizard that is what
it cam up with. I will just try to paste the sql from the query and just
paste it in the sql for the chart to see if that works. What you suggested
work just fine it was the chart that gave me someting different.
:
What happened to my suggestion?
SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS
[Count]
FROM [BILLING LOG]
WHERE [DATE TO PRINT ROOM] Between Forms!date_frm!srtdate AND
Forms!date_frm!endDate
GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT
ROOM])*12+Month([DATE TO PRINT ROOM])-1
ORDER BY Year([DATE TO PRINT
ROOM])*12+Month([DATE TO PRINT ROOM])-1
--
Duane Hookom
Microsoft Access MVP
:
this is the row source for the chart:
SELECT [MMMYY],Sum([Count]) AS [SumOfCount] FROM [orders_completed] GROUP
BY [MMMYY];
:
Check your Row Source property. The order displayed in the datasheet of this
sql should be the order the chart displays. If you don't figure this out,
come back with the Row Source property.
--
Duane Hookom
Microsoft Access MVP
:
Ok I got that and understand thanks, but now i have a problem with the chart
that i am creating. When I create the chart it still shows the dates out of
order, any cues?
Thanks so much!
:
Make sure your form is open with dates entered and change your Row Source
query to:
SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS MMMYY, Count(*) AS
[Count]
FROM [BILLING LOG]
WHERE [DATE TO PRINT ROOM] Between Forms!date_frm!srtdate AND
Forms!date_frm!endDate
GROUP BY Format([DATE TO PRINT ROOM],"mmm"" '""yy"), Year([DATE TO PRINT
ROOM])*12+Month([DATE TO PRINT ROOM])-1
ORDER BY Year([DATE TO PRINT
ROOM])*12+Month([DATE TO PRINT ROOM])-1;
This assumes your field name is [Date To Print Room].
--
Duane Hookom
Microsoft Access MVP
:
my form:
date_frm
srtdate
enddate
my sql:
SELECT (Format([DATE TO PRINT ROOM],"mmm"" '""yy")) AS Expr1, Count(*) AS
[Count]
FROM [BILLING LOG]
GROUP BY (Format([DATE TO PRINT ROOM],"mmm"" '""yy")), (Year([DATE TO PRINT
ROOM])*12+Month([DATE TO PRINT ROOM])-1)
HAVING ((Year([DATE TO PRINT ROOM])*12+Month([DATE TO PRINT ROOM])-1));
:
Create your form with text boxes and provide us with the names like:
frmSelectDates
txtStart
txtEnd
Provide us with your current SQL view like:
Select Format([SomeDateField],"mmm-yyyy"), Count(*) as NumOf
From tblYourTable
Group By Format([SomeDateField],"mmm-yyyy"),
Format([SomeDateField],"yyyymm")
Order By Format([SomeDateField],"yyyymm");
--
Duane Hookom
Microsoft Access MVP
:
Thank you for your response. I still am very confused. I Have a table
[billing log] and I created a query that has the dates from the [billing log]
formatted to show the month-year example jan-07 and the second column in the
query to count the number of records that fall in that month. I have it
showing the right amounts and dates. The problem i am having is how to group
by year and month and how to incorperate the text boxes on the form that you
wrote back about.
:
The first task is to create a form with a couple text boxes for you to enter
the beginning and ending dates. Then create a query that groups by Year and
Month and calculates some value.
Then use the report chart wizard based on the query. The Row Source of the
chart control is the key to what displays in your chart.
--
Duane Hookom
Microsoft Access MVP
:
I am trying to create my first chart in a report. I would like to know how to
set the data in the x axis to show the month-year example Jan-07. I would
like it to ask when to start the first month and the last month and then put
it in order of the month and year. I don't know if this makes sense. If not
maybe i can try to explain it a different way. Thanks for any help.
I am not sure why but i trust you.