New Issue...Graphs

[thewabit via AccessMonster.com]

How would I write a function or a module to tell a month that is displayed in
the table as a number to display on the graph as abbreviations such as Oct,
Nov, Jan, etc?

 

[ghetto_banjo]

there is a handy function called MonthName()

MonthName( number, abbreviate?)


example:

MonthName(3, TRUE) = Mar

MonthName(3, FALSE) = March

MonthName(3) = March

 

[thewabit via AccessMonster.com]

Thanks Ghetto,

I think I will be able to use that so let me take my question to the next
step. I have a graph based on a query. The data the query is grabbing for
"month" is nymerical and I want to change it to *display* the month on the
graph as Nov, Dec< Jun, etc. How do I take the function language you provided
and run that function with this query?

I am such a newbie!

 

[ghetto_banjo]

this might be a duplicate post, my first isnt showing up on my screen.

anyways,


if you are using this month name for your x-axis on the graph, then i
gave bad advice above. you dont want to use that function as it
returns a string which access will not sort in "correct" chronological
order.

instead, send the number just like you have been doing. right click
on the graph, go to Chart Object --> Edit

RIght click on the x-axis, and click Format Axis.

Go to the Number tab. Change the Category to Custom, and change the
Type: mmm


mmm as the type will display your number as the abbreviated month
format, and access will be smart enough to keep things in order.


i think this is the way to go about it if you are using the month name
for an axis.

 

[thewabit via AccessMonster.com]

Ok...that worked! Now...you said to "send the number as you are now". (I
think) the query is sending the number. (In this example it is 10). But the
display is still showing Jan. It should be returning Oct. Any ideas?

 

[ghetto_banjo]

hmm not sure what to say about that one. is it coming up Jan for
every number?

 

[thewabit via AccessMonster.com]

I have sort of got it working but it is not quite correct.
Can you tell me what this calculation means in plain english? It is in row
source query of my graph.
(Year([OBMonth])*12+Month([OBMonth])-1)

 

[ghetto_banjo]

i guess ObMonth is a date/time field?

the year function returns the Year of a date, and the Month function
returns the month.

i.e. Year(#01/21/2010#) = 2010
and Month(#01/21/2010#) = 1

so that function is taking the year value of ObMonth, multiplying by
12, then Adding the month value of ObMonth, and subtracting 1 from
that.

so say ObMonth = #01/21/2010#

then Year([ObMonth])*12 + Month ([OBMonth]) - 1
=
2010 * 12 + 1 - 1 = 24120

Not sure what that value is supposed to describe in your row source.

 

[thewabit via AccessMonster.com]

I'm not sure either. I am trying to display the month AND year in a format
like: "Jan '10" in the x axis of the chart coinciding with a plot mark
depending on an item in a listbox next to it. For example, the list box has a
number displayed ased on a query I have ran. When I select the item in the
list box, that number shows up on the graph.

I am assuming that 24120 that it is calculating is somehow getting the year
to show up in the x axis but for now I can only get the month to work.

Here is the SQL for the graph:

SELECT MonthDisplay([OBMonth]) AS Expr1, Sum(qryLOSATrend_HF.HumanFactorQTY)
AS SumOfHumanFactorQTY
FROM qryLOSATrend_HF
GROUP BY MonthDisplay([OBMonth]), (Year([OBMonth])*12+Month([OBMonth])-1)
ORDER BY (Year([OBMonth])*12+Month([OBMonth])-1);

i guess ObMonth is a date/time field?

the year function returns the Year of a date, and the Month function
returns the month.

i.e. Year(#01/21/2010#) = 2010
and Month(#01/21/2010#) = 1

so that function is taking the year value of ObMonth, multiplying by
12, then Adding the month value of ObMonth, and subtracting 1 from
that.

so say ObMonth = #01/21/2010#

then Year([ObMonth])*12 + Month ([OBMonth]) - 1
=
2010 * 12 + 1 - 1 = 24120

Not sure what that value is supposed to describe in your row source.

 

[thewabit via AccessMonster.com]

I'm getting closer.

What if it was the last 2 numbers of the year. Does that calculation make
sense knowing that the ultimate goal is to display month in mmm and last 2
digits of year in graph?

i guess ObMonth is a date/time field?

the year function returns the Year of a date, and the Month function
returns the month.

i.e. Year(#01/21/2010#) = 2010
and Month(#01/21/2010#) = 1

so that function is taking the year value of ObMonth, multiplying by
12, then Adding the month value of ObMonth, and subtracting 1 from
that.

so say ObMonth = #01/21/2010#

then Year([ObMonth])*12 + Month ([OBMonth]) - 1
=
2010 * 12 + 1 - 1 = 24120

Not sure what that value is supposed to describe in your row source.