Results 1 to 8 of 8
  1. #1
    allenjasonbrown@gmail.com is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    44

    Case or Switch in an update statement


    Hello -
    I am trying to create an update statement to a table. I need to use a case statement or switch based on a date field.
    Here is pseudo code for what I am trying to do:
    Code:
    update table1 
    set periodnumber = case mydate1
    when  01/01/09 to 01/31/09 then 'Period1'
    when  02/01/09 to 02/31/09 then 'Period2'
    when  03/01/09 to 03/31/09 then 'Period2'
    etc......
    end case
    Can someone help with the proper syntax or with the best way to accomplsh this?? Basically just trying to look at a date field in a record and update the period field.

    Thank you in advance.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Create a Procedure in a standard module the returns the string using whatever process you want and then the update query becomes:
    update table1 set periodnumber = YourProcedure(mydate1)

  3. #3
    allenjasonbrown@gmail.com is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    44
    Hi....it is the "whatever process you want" I am trying to figure out.

    I am trying to figure out the proper syntax for a case statement using between/to dates.

    case field1
    when field1 between 01/01/09 and 01/31/09 then = 'PERIOD1'
    when field1 between 02/01/09 and 02/31/09 then = 'PERIOD2'
    etc.....
    end

    Thank you and sry for the confusion.....

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Will the results *always* be a Month number?

  5. #5
    allenjasonbrown@gmail.com is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    44
    technically the results will be Y09P01, Y09P02, etc......

    and yes......always corresponding to year and month

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The start of your function:
    Code:
    Public Function YourProcedure(InValue As Date) As String
       Select Case InValue
          Case #1/1/2009# To #1/31/2009#
             YourProcedure = "Period1"
          Case #2/1/2009# To #2/28/2009#
             YourProcedure = "Period2"
          Case Else
             YourProcedure = "Unknown!"
       End Select
       
    End Function

  7. #7
    allenjasonbrown@gmail.com is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    44
    Hey RuralGuy - I am still having issues with the syntax.

    I used just the select and it is returning the wrong values???

    for example a date of '7/10/2003' is being picked up by " Case #7/1/2000# To #7/31/2000#"

    it should only be true with " Case #7/1/2003# To #7/31/2003#"

    It seems to be ignoring the year????? Any thoughts?? Please help??

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You need to feed the Function I posted a DateTime value, *not* a string. If it will be a string then it would be written differently.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update Statement in form code
    By ksmith in forum Programming
    Replies: 9
    Last Post: 11-07-2011, 12:04 PM
  2. Update statement with linked table join
    By Guigui in forum Queries
    Replies: 6
    Last Post: 09-17-2010, 04:47 AM
  3. Replies: 2
    Last Post: 06-14-2010, 09:38 PM
  4. Replies: 6
    Last Post: 04-06-2010, 03:00 PM
  5. Replies: 6
    Last Post: 03-27-2010, 11:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums