query displays wrong month name

[Guest]

Hello,

I am beginning query builder. I have a select query which should extract sales data for one month after the user is prompted for the year and month. It should display the sales totals for each category with the month name written out. I want to use it to create a monthly report of sales.

The problem is I can run it without the Format function and get the month to display correctly as a number but if I try to use Format to get the month name I get the wrong month. If I enter 1 (for January) I get Dec, 2 (for February) I get Jan, 12 (for December) I get Jan. Why is this happening and how can I get the correct month to print out?
I also tried separating the Format and DatePart statements with no good effect.
The SQL follows:

Thanks for your help.

SELECT Format(DatePart("m",[DateSequenced]),"mmm") AS [Month], Prices.ProcedureName, Sum(Prices.Price) AS Charges
FROM Prices INNER JOIN Sample ON Prices.PriceCode = Sample.PriceCode
WHERE (((DatePart("yyyy",[DateSequenced]))=[enter year]) AND ((DatePart("m",[DateSequenced]))=[enter month]))
GROUP BY Format(DatePart("m",[DateSequenced]),"mmm"), Prices.ProcedureName;

 

[Guest]

Sorry I forgot to say I'm using Access2000. And the user enters the month number when prompted

----- Lee wrote: ----

Hello

I am beginning query builder. I have a select query which should extract sales data for one month after the user is prompted for the year and month. It should display the sales totals for each category with the month name written out. I want to use it to create a monthly report of sales

The problem is I can run it without the Format function and get the month to display correctly as a number but if I try to use Format to get the month name I get the wrong month. If I enter 1 (for January) I get Dec, 2 (for February) I get Jan, 12 (for December) I get Jan. Why is this happening and how can I get the correct month to print out
I also tried separating the Format and DatePart statements with no good effect
The SQL follows

Thanks for your help

SELECT Format(DatePart("m",[DateSequenced]),"mmm") AS [Month], Prices.ProcedureName, Sum(Prices.Price) AS Charge
FROM Prices INNER JOIN Sample ON Prices.PriceCode = Sample.PriceCod
WHERE (((DatePart("yyyy",[DateSequenced]))=[enter year]) AND ((DatePart("m",[DateSequenced]))=[enter month])
GROUP BY Format(DatePart("m",[DateSequenced]),"mmm"), Prices.ProcedureName

 

[ExcellentGent]

Since query parameters have determined the year and month, you can remove the
DatePart function from the SELECT statement and the GROUP BY clause:

SELECT Format([DateSequenced]),"mmm") AS [Month], etc.

GROUP BY Format([DateSequenced]),"mmm"), etc.

(You can use the string expression "mmmm" in the Format function to display the
full name of a month.)

 

[Guest]

Thank you! It works just fine now.
Lee

 

[Ken Snell]

FYI...not a good practice to use Month, Year, Date, Day, Name, Value, etc.
as names of fields or controls. Those words (and others) are reserved words
in ACCESS because they are VBA functions or the methods/properties of
controls or forms or reports. ACCESS can become very confused if you use
such a word and forget to put [ ] around it -- the confusion can rise to the
level of ACCESS refusing to recognize the difference between what you want
and what it does, and can cause you to either rebuild a form/query or even
the database.