Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2007
    Posts
    6

    Unanswered: Creating bins based on date

    Hi:
    I'm a new access user. I'm trying to create a query where Access looks at the date and then returns a season. I think this would be an IIF statement, but I cannot figure out one that will work. Can anyone help?

    Example. I'm storing the date in the field [Boarding Date].
    Winter (11/16-2/29)
    Spring (3/1-5/30)
    Summer (6/1-9/15)
    Fall (9/16-11/15)

    Here is what I was thinking:

    Season:IIf([Boarding Date] = between 11/16/## and 2/29/##, winter, IIf([Boarding Date] =between 3/1/## and 5/30/##, spring, IIf([Boarding Date] = between 6/1/## and 9/15/##, summer, IIf([Boarding Date] = between 9/16/## and 11/15/##, fall))))

    I don't want to be year specific, since I want this to work no matter which date year the query is working with. What is wrong with this syntax?

    Thanks- Biodata

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well I'll start with these:

    * there is no such logical operator as =Between, there is = and there is Between. They are different.
    * you're going to need to be using Month() and Day() functions to dodge the year issue.
    * you need to enclose strings in double quotes, such as "Winter".

    And a comment is that this expression is gonna be a doozie!

    ...and wtf is fall? lol

    We have it easy here in Australia. All seasons here start at the first of a month. Makes this kind of thing a LOT easier. TBH, I didn't know that elsewhere in the world, for some weird reason, seasons can start mid-month. Seems pretty weird to me. I woulda thought a season was 3 months
    Last edited by StarTrekker; 02-05-08 at 21:16.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    According to our calendar, winter doesn't start until mid December... Hah!
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you would be far better off creating a table which stores the dates. then you can get rid of the IIF and use a join to retrieve the season.
    if you don't want to go down the table route then I suppose a function may do the job. otherwise you are going to end up with an extremely long difficult to debug IIF statement.

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    The iif is ugly past the first or second condition.

    If you want to use a function, use Select ... Case. It accomplishes the exact same thing, but is a LOT easier to write, understand and debug.

    Code:
    Function GetSeason(varDate As Variant) As String
        Dim sD As String
    
        If IsNull(varDate) Then
            ...Do something
        Else
            sD = Format(Me.txtDate,"MMDD")
            Select Case sD
                Case Between 0301 And 0530
                    GetSeason = "Spring"
                Case Between 0601 And 0915
                    GetSeason = "Summer"
                Case Between 0916 And 1115
                    GetSeason = "Fall (aka Autumn to the rest of the world)"
                Case Else
                    GetSeason = "Winter"
            End Select
        End If
    End Function
    You can place =GetSeason([txtDate]) in the recordsource property of the season text box.

    You can use the function in a query also if you add Public before Function and place the code in a Module.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  6. #6
    Join Date
    Nov 2007
    Posts
    6

    Thank you

    Thank you all for your help. I know the date break downs are weird, but they are based on environmental conditions rather than typical calendar seasons. I will attempt the Select...Case code. I have not done that before. Like I said, I'm a newbie. Thanks for taking the time to reply.

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I prefer the approach tcace has taken, but there are some syntax issues with code that was posted. It might be a little frustrating to a newbie to try to debug. Here is a working version:

    Code:
    Public Function GetSeason(varDate As Variant) As String
        Dim sD As String
    
        If IsNull(varDate) Then
            
        Else
            sD = Format(varDate, "MMDD")
            Select Case sD
                Case "0301" To "0530"
                    GetSeason = "Spring"
                Case "0601" To "0915"
                    GetSeason = "Summer"
                Case "0916" To "1115"
                    GetSeason = "Fall"
                Case Else
                    GetSeason = "Winter"
            End Select
        End If
        
    End Function
    To test it, open a new module, copy and paste in this code, then go to View/Immediate Window and type in the following command and press enter:

    ?GetSeason(#3/1/08#)

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Sorry for the syntax issues- I was "coding on the fly" and did not write it in the module editor.

    good luck!
    tc

Posting Permissions

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