Month Display

[Barryg]

OK,


I would like to ask a question about dates.
Here is my SQL for a query that returns a QTY completed by Month and Year.

SELECT Count(*) AS QtyComplete, Month([BRF_BOF_Complete]) AS TheMonth, Year([BRF_BOF_Complete]) AS TheYear
FROM tblNPI
WHERE (((tblNPI.Status_ID)=12 Or (tblNPI.Status_ID)=17))
GROUP BY Month([BRF_BOF_Complete]), Year([BRF_BOF_Complete])
HAVING (((Year([BRF_BOF_Complete]))=2014));

The result is this:

# BRFcompletedByMonth
QtyComplete	TheMonth	TheYear
9	1	2014
13	2	2014
13	3	2014
2	4	2014

What I want is to display the month as Jan, Feb, Mar, Apr…Dec. instead of 1, 2, 3, 4 etc. How do I accomplish that?
Also if I do that will it migrate to the reports when I create a report for that query?

[RuralGuy]

Have you looked into the MonthName() function?

[Barryg]

Yes,
I did look at that, but got confused on how to make it work with my sql statement which is why I came to the forum.


thank you

[aellistechsupport]

If you create a report and use labels you can use this
http://www.access.hookom.net/Dynamic...rosstabRpt.htm

Basically it says:
Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)
This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
Column Headings: "Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months.

If you need column labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)

--------------
http://www.utteraccess.com/forum/Rol...s-t366268.html
Month1: Format(Now(),"mmm")
Month2: Format(DateAdd("m",1,Now()),"mmm")
Month3: Format(DateAdd("m",2,Now()),"mmm")
Month4: Format(DateAdd("m",3,Now()),"mmm")
Month5: Format(DateAdd("m",4,Now()),"mmm")
Month6: Format(DateAdd("m",5,Now()),"mmm")
Month7: Format(DateAdd("m",6,Now()),"mmm")
Month8: Format(DateAdd("m",7,Now()),"mmm")
Month9: Format(DateAdd("m",8,Now()),"mmm")
Month10: Format(DateAdd("m",9,Now()),"mmm")
Month11: Format(DateAdd("m",10,Now()),"mmm")
Month12: Format(DateAdd("m",11,Now(),),"mmm")

[RuralGuy]

Yes,
I did look at that, but got confused on how to make it work with my sql statement which is why I came to the forum.


thank you

How about MonthName(Month([BRF_BOF_Complete])) AS theMonthName