Format month in crosstab

[Sheila D]

I have a crosstab query which groups by year and by month using Year and
Month functions respectively. The Month([Date_Of_Procedure]) returns the
correct month number but I cannot format it to show the month name; when I
use the format property everything ends up as January as (I guess) it is
using the julienne number thus everything 1 - 12 is January 1900?

Can I get round this without creating a linked table to show correct month
name or will that be the easiest answer (I'm not great at VB so simple
answers preferred)
Thanks, Sheila

 

[Allen Browne]

Try:
Format([Date_Of_Procedure], "mmm")

Then use the Column Headings property of the crosstab to teach it the order
of the months.

 

[Lars Brownie]

Check out this excellent article with download:

http://www.fmsinc.com/TPapers/access/Reports/monthly/index.html

It's an example on how to create a monthly report without any code.

Lars

 

[Sheila D]

Hi Allen

I've tried the Format property as you suggest but I'm not sure what you mean
by use the Column Headings property of the crosstab to teach it the order of
the months? I'm using the month as a Rowheading
Thanks, Sheila

Try:
Format([Date_Of_Procedure], "mmm")

Then use the Column Headings property of the crosstab to teach it the order
of the months.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a crosstab query which groups by year and by month using Year and
Month functions respectively. The Month([Date_Of_Procedure]) returns the
correct month number but I cannot format it to show the month name; when I
use the format property everything ends up as January as (I guess) it is
using the julienne number thus everything 1 - 12 is January 1900?

Can I get round this without creating a linked table to show correct month
name or will that be the easiest answer (I'm not great at VB so simple
answers preferred)
Thanks, Sheila

 

[Allen Browne]

Month as RowHeading will work, but Apr comes before Feb (Because A comes
before F.)

To fixt that, open the Properties box (in query design view), and look at
the properties of the query (not of a table or a field.) For a crosstab
query, there is a property named Column Headings.

More info:
http://allenbrowne.com/ser-67.html#ColHead

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Check out this excellent article with download:

http://www.fmsinc.com/TPapers/access/Reports/monthly/index.html

It's an example on how to create a monthly report without any code.

Lars

I have a crosstab query which groups by year and by month using Year and
Month functions respectively. The Month([Date_Of_Procedure]) returns the
correct month number but I cannot format it to show the month name; when
I
use the format property everything ends up as January as (I guess) it is
using the julienne number thus everything 1 - 12 is January 1900?

Can I get round this without creating a linked table to show correct
month
name or will that be the easiest answer (I'm not great at VB so simple
answers preferred)
Thanks, Sheila

 

[Sheila D]

I've set the column headings property to
"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"
This works perfectly if I change the Month to a Column Heading but does not
sesem to have any impact when I leave it as a Row Heading. Am I mossing
something, thanks for all your help
Sheila

Month as RowHeading will work, but Apr comes before Feb (Because A comes
before F.)

To fixt that, open the Properties box (in query design view), and look at
the properties of the query (not of a table or a field.) For a crosstab
query, there is a property named Column Headings.

More info:
http://allenbrowne.com/ser-67.html#ColHead

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Check out this excellent article with download:

http://www.fmsinc.com/TPapers/access/Reports/monthly/index.html

It's an example on how to create a monthly report without any code.

Lars

I have a crosstab query which groups by year and by month using Year and
Month functions respectively. The Month([Date_Of_Procedure]) returns the
correct month number but I cannot format it to show the month name; when
I
use the format property everything ends up as January as (I guess) it is
using the julienne number thus everything 1 - 12 is January 1900?

Can I get round this without creating a linked table to show correct
month
name or will that be the easiest answer (I'm not great at VB so simple
answers preferred)
Thanks, Sheila

 

[Allen Browne]

Sorry, Sheila: I misread you.

Totally unnecessary for RowHeadings.

 

[Sheila D]

But then how do I get the months to sort correctly as a Row Heading.......I
end up with Apr, Aug etc

 

[Allen Browne]

Use 2 row headings: the month number, and the month name.

Sort by the month number.

 

[Sheila D]

Thank you so much

Use 2 row headings: the month number, and the month name.

Sort by the month number.