CODEWITHSUNDEEP
ms-accessms-access-2007
user3065287
user3065287

Reputation: 15

Access Group By Month Names Using Format Function

I am Unable to Sort the Order By by Month names in MS Access Database

I am Using the Format Function to retrieve Month Name (If I use MonthName(Month(date)) Function The Query is Executing at Access DB(but when I am trying to Use the Same Query from My Application I am getting Errors

My Query is

This is My Query(Code )Used all the Functions of Format But I am Unable to Order By MonthNames

SELECT Format(date ,"mmmm") as MonthName,
 Sum(Rojnamchatable.asal1) AS SumOfasal1,
 Sum(Rojnamchatable.chaat1) AS SumOfchaat1,
 SumOfasal1+SumOfchaat1 AS TotalBiri1,
 Sum(Rojnamchatable.asal2) AS SumOfasal2, 
Sum(Rojnamchatable.chaat2) AS SumOfchaat2, 
SumOfasal2+SumOfchaat2 AS TotalBiri2,

GROUP BY Format(date ,"mmmm")
ORDER BY Format(date ,"mmmm")


I am Getting Data Perfectly But I am unable to get Order By Month Name
(i.e Jan ,Feb ,March)

I Even Used Format(date,"mm") Function If I Use that I am getting Erors

So Please try to Solve My Issue

I Spent 2 days for this

Upvotes: 0

Views: 1927

Answers (1)

PaulFrancis
PaulFrancis

Reputation: 5809

How about adding the month (number) column but not displaying it?

SELECT 
    Format([date] ,"mmmm") as MonthName,
    Sum(Rojnamchatable.asal1) AS SumOfasal1,
    Sum(Rojnamchatable.chaat1) AS SumOfchaat1,
    SumOfasal1+SumOfchaat1 AS TotalBiri1,
    Sum(Rojnamchatable.asal2) AS SumOfasal2, 
    Sum(Rojnamchatable.chaat2) AS SumOfchaat2, 
    SumOfasal2 + SumOfchaat2 AS TotalBiri2,
FROM 
    yourTableName
GROUP BY 
    Format([date] ,"mmmm"), Month(date)
ORDER BY 
    Month([date])

Please note I have enclosed date in [], this is because of the fact Date is a reserved word and should not be used as a column/field name.

Upvotes: 2

Related Questions