Search code examples
sqlms-accesssql-view

Convert Month Number to Month Name Function on Access

My good reference has been Convert Month Number to Month Name Function in SQL

So far I have: 

SELECT Detail.ItemCode, SUM(Detail.QuantityOrdered) AS Total_Quantity, Header.OrderDate
FROM Detail INNER 
JOIN rHeader ON Detail.SalesOrderNo = Header.SalesOrderNo
WHERE Header.OrderDate >= dateadd("m", -4, Date())
GROUP BY Detail.ItemCode, OrderDate
ORDER BY SUM(Detail.QuantityOrdered) DESC;

It filters my results and it shows only last four months result from today's month.

I'd like to have each month's sales quantity sum, and its month to name function.

For instance:

ItemCode | 10 or October | 11  |  12 |  1     
   PM    |        200    | 200 | 200 | 200

Update: I did try the following line of code, but my date is in the form of 12/26/2016. Is there anyway that I can play with it?? Thank you!

 SELECT MonthName( month , DateAdd( month , @MonthNumber , 0 ) - 1 )
Solution

  • In an Access query you should be able to use the MonthName function to convert a month number (e.g., 10) into a month name (e.g., 'October').

    For example, if you have a query that returns a date of birth (DOB)...

    SELECT [DOB] FROM [Clients] WHERE [ID]=1

    DOB
----------
1977-05-15

    .. you can return the name of the month in which they were born using

    SELECT MonthName(Month([DOB])) AS MonthOfBirth FROM [Clients] WHERE [ID]=1

    MonthOfBirth
------------
May