Use DatePart to assign "seasons"?

Datamigo · #1 (**permalink**) 01-04-12, 14:02

Hi gang!
I'm modifying an Access 2007 db to add a "Season" field to existing invoices. I added a list box with "winter" "spring" "summer" and "fall" to the Invoice table and form.
I'm trying to make these entries correspond to InvoiceDate using DatePart "m", but am not sure how to make it show the months 10,11,12,01,02, and 03 as "winter"...04,05,06, as "spring" etc.

Am I going about this all wrong? I would greatly appreciate any tips you have!!!

Sinndho · #2 (**permalink**) 01-05-12, 05:08

You could try:

Code:

SELECT TableName.*, 
IIf(Month(TableName.SomeDate) In (3,4,5),'Spring',
    IIf(Month(TableName.SomeDate) In (6,7,8),'Summer',
        IIf(Month(TableName.SomeDate) In (9,10,11),'Autumn', 'Winter'))) AS Season
FROM TableName
WHERE TableName.SomeDate Is Not Null;

Datamigo · #3 (**permalink**) 01-05-12, 14:57

Thank you Sinndho! That did work, but I realized I couldn't use it for the report I wanted because I needed it to span several years.

...Now I have a new problem:

I have this column in a query:
Field: Month: Month([InvoiceDate])
Criteria: Between [Enter Starting Month] And [Enter Ending Month]

When I enter 10 (october) for the starting month and 3 (March) for an ending month, I get results for 10, 11, 12, 2, and 3 but not 1 (janurary).

When I change the Format to "m" or "mm", I only get 1 (janurary) as a result.
If I remove the Between criteria, I get all the months.

...and here I thought my ex-wife was confusing.

Sinndho · #4 (**permalink**) 01-05-12, 15:37

Quote:

Originally Posted by Datamigo

When I change the Format to "m" or "mm", I only get 1 (janurary) as a result.

Which format? Month() is a function that, for a given date, returns an Integer value from 1 through 12 representing the month of the year. There's no format in there.

Datamigo · #5 (**permalink**) 01-05-12, 22:22

In the queary, I select (or highlight) the Month column and open the Property Sheet. It shows a Format row and I was trying different options there.

It's weird. When I try 10 through 12 or 5 through 10 or any combination within 1 and 12 -I get the right results. It's only when I try a combination that carries through a new year (i.e. 11 through 3) does it omit the 1.

Sinndho · #6 (**permalink**) 01-06-12, 02:04

Can you please post the SQL of the whole query?

Datamigo · #7 (**permalink**) 01-06-12, 14:33

Here ya go:

Code:

SELECT Invoice.JobType, Invoice.JobStatus, DatePart("yyyy",[InvoiceDate]) AS [Year], Month([InvoiceDate]) AS [Month], Invoice.InvoiceDate, ([TotalPrice1]+[TotalPrice2]+[TotalPrice3]+[TotalPrice4]+[TotalPrice5]+[TotalPrice6]+[TotalPrice7]+[TotalPrice8]+[TotalPrice9]+[TotalPrice10]+[TotalPrice11]+[TotalPrice12]+[TotalPrice13]+[TotalPrice14]+[TotalPrice15]+[TotalPrice16]+[TotalPrice17]+[TotalPrice18]+[TotalPrice19]+[TotalPrice20]) AS TotalMaterials, Invoice.TotalLabor, Invoice.DeliveryCharge, Invoice.ExtraLabor, ([TotalMaterials]+[TotalLabor]+[DeliveryCharge]+[ExtraLabor]) AS SubTotal, Invoice.SalesTaxRate, [SubTotal]*[SalesTaxRate] AS SalesTax, [SubTotal]+[SalesTax] AS TotalDue, Invoice.QTY1, Invoice.PriceEach1, [QTY1]*[PriceEach1] AS TotalPrice1, Invoice.QTY2, Invoice.PriceEach2, [QTY2]*[PriceEach2] AS TotalPrice2, Invoice.QTY3, Invoice.PriceEach3, [QTY3]*[PriceEach3] AS TotalPrice3, Invoice.QTY4, Invoice.PriceEach4, [QTY4]*[PriceEach4] AS TotalPrice4, Invoice.QTY5, Invoice.PriceEach5, [QTY5]*[PriceEach5] AS TotalPrice5, Invoice.QTY6, Invoice.PriceEach6, [QTY6]*[PriceEach6] AS TotalPrice6, Invoice.QTY7, Invoice.PriceEach7, [QTY7]*[PriceEach7] AS TotalPrice7, Invoice.QTY8, Invoice.PriceEach8, [QTY8]*[PriceEach8] AS TotalPrice8, Invoice.QTY9, Invoice.PriceEach9, [QTY9]*[PriceEach9] AS TotalPrice9, Invoice.QTY10, Invoice.PriceEach10, [QTY10]*[PriceEach10] AS TotalPrice10, Invoice.QTY11, Invoice.PriceEach11, [QTY11]*[PriceEach11] AS TotalPrice11, Invoice.QTY12, Invoice.PriceEach12, [QTY12]*[PriceEach12] AS TotalPrice12, Invoice.QTY13, Invoice.PriceEach13, [QTY13]*[PriceEach13] AS TotalPrice13, Invoice.QTY14, Invoice.PriceEach14, [QTY14]*[PriceEach14] AS TotalPrice14, Invoice.QTY15, Invoice.PriceEach15, [QTY15]*[PriceEach15] AS TotalPrice15, Invoice.QTY16, Invoice.PriceEach16, [QTY16]*[PriceEach16] AS TotalPrice16, Invoice.QTY17, Invoice.PriceEach17, [QTY17]*[PriceEach17] AS TotalPrice17, Invoice.QTY18, Invoice.PriceEach18, [QTY18]*[PriceEach18] AS TotalPrice18, Invoice.QTY19, Invoice.PriceEach19, [QTY19]*[PriceEach19] AS TotalPrice19, Invoice.QTY20, Invoice.PriceEach20, [QTY20]*[PriceEach20] AS TotalPrice20
FROM Invoice INNER JOIN Customers ON Invoice.[CustomerID#] = Customers.[CustomerID#]
WHERE (((Invoice.JobType)="b") AND ((Invoice.JobStatus)="i") AND ((Month([InvoiceDate])) Between [Enter Starting Month] And [Enter Ending Month]))
ORDER BY Month([InvoiceDate]);

Sinndho · #8 (**permalink**) 01-06-12, 16:33

This is really strange. You could try to replace the Month() function by the DatePart() function (which returns a Variant) an see what happens:

Code:

((DatePart("m", [InvoiceDate])) Between [Enter Starting Month] And [Enter Ending Month]))

Although I don't believe this is the cause of the problem you describe, I would also change two aliases that could confuse the Jet Engine because Month and Year are reserved words :

Code:

DatePart("yyyy",[InvoiceDate]) AS InvoiceYear, 
Month([InvoiceDate]) AS InvoiceMonth,

Datamigo · #9 (**permalink**) 01-06-12, 17:06

I will try that...Thanks so much for your help!

Sinndho · #10 (**permalink**) 01-06-12, 17:08

You're welcome!

Internet Services

Web Development

Digital Marketing

Consumer Tech