Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change date automatically (2002, SR 3)

    I am trying to make the BegDate and EndDate change automatically when a form is opened based on the current date using a Select Case statement. The last case is simply a test for the current date period. The code simply changes the date to whichever case is first in the list. What is wrong with my logic. Is there another way this can be done?

    Private Sub Form_Open(Cancel As Integer)
    On Error GoTo ErrHandler
    DoCmd.SetWarnings False
    Select Case Date

    Case Is > 9 / 4 / 2006, Is < 11 / 7 / 2006
    DoCmd.OpenQuery "qryFirst9wk"
    Me.Requery
    Exit Sub

    Case Is > 11 / 7 / 2006, Is < 1 / 27 / 2007
    DoCmd.OpenQuery "qrySecond9wk"
    Me.Requery
    GoTo ExitHandler

    Case Is > 1 / 29 / 2007, Is < 3 / 31 / 2007
    DoCmd.OpenQuery "qryThird9wk"
    Me.Requery
    GoTo ExitHandler

    Case Is > 4 / 9 / 2007, Is < 6 / 15 / 2007
    DoCmd.OpenQuery "qryFourth9wk"
    Me.Requery
    GoTo ExitHandler

    Case Is > 7 / 1 / 2006, Is < 7 / 30 / 2006
    DoCmd.OpenQuery "qryTest9wk"
    Me.Requery
    GoTo ExitHandler
    End Select

    ExitHandler:
    DoCmd.SetWarnings True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler

    End Sub
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change date automatically (2002, SR 3)

    To include literal dates in code, you must enclose them in # characters, otherwise VBA thinks you are performing a division.
    You shouldn't use two conditions for each Case instruction, if you use Case A, B it is interpreted as If A Or B Then, not as If A And B Then.
    You don't need Exit Sub or GoTo ExitHandler in each Case, for as soon as a condition is True, execution jumps to past the End Select.
    Since you execute Me.Requery in each case, you can place it after End Select.

    Try the following:

    Private Sub Form_Open(Cancel As Integer)
    On Error GoTo ErrHandler
    DoCmd.SetWarnings False

    Select Case Date
    Case Is < #11/7/2006#
    DoCmd.OpenQuery "qryFirst9wk"

    Case Is < #1/27/2007#
    DoCmd.OpenQuery "qrySecond9wk"

    Case Is < #3/31/2007#
    DoCmd.OpenQuery "qryThird9wk"

    Case Is < #6/15/2007#
    DoCmd.OpenQuery "qryFourth9wk"

    Case Is < #7/30/2006#
    DoCmd.OpenQuery "qryTest9wk"
    End Select

    Me.Requery

    ExitHandler:
    DoCmd.SetWarnings True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change date automatically (2002, SR 3)

    Thanks. That worked great. I had to move my test that was last in the select case statements so that it appeared first in the list but after I did that I changed the date on my computer to test for various dates and it worked perfectly.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

Posting Permissions

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