Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2012
    Posts
    5

    Unanswered: Use DatePart to assign "seasons"?

    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!!!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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;
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    5
    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.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Datamigo View Post
    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.
    Have a nice day!

  5. #5
    Join Date
    Jan 2012
    Posts
    5
    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.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can you please post the SQL of the whole query?
    Have a nice day!

  7. #7
    Join Date
    Jan 2012
    Posts
    5
    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]);

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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,
    Have a nice day!

  9. #9
    Join Date
    Jan 2012
    Posts
    5
    I will try that...Thanks so much for your help!

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •