Help with Function: date range

Discussion in 'Microsoft Access Getting Started' started by Bre-x, Apr 14, 2010.

  1. Bre-x

    Bre-x Guest

    Public Function my_dates(the_date As Date) As Integer

    Select Case the_date
    Case Between "07/31/2005" And "08/01/2006"
    my_dates = 2006
    case Between "07/31/2006" And "08/01/2007"
    my_dates = 2006
    Case Else
    my_date = 0
    End Select

    End Function

    If try to use # i get the same error

    Help!!

    Thank you
    Bre-x
     
    Bre-x, Apr 14, 2010
    #1
    1. Advertisements

  2. Bre-x

    Bre-x Guest

    Public Function my_dates(the_date As Date) As Integer

    Select Case the_date

    Case #7/31/2005# To #8/1/2006#
    my_dates = 2006
    Case #7/31/2006# To #8/1/2007#
    my_dates = 2007
    Case #7/31/2007# To #8/1/2008#
    my_dates = 2008
    Case #7/31/2008# To #8/1/2009#
    my_dates = 2009
    Case #7/31/2009# To #8/1/2010#
    my_dates = 2009
    Case Else
    my_date = 0
    End Select
    End Function

    Thank you!!!
     
    Bre-x, Apr 14, 2010
    #2
    1. Advertisements

  3. Bre-x

    Wayne-I-M Guest

    datefield > #12/34/5678# AND datefield < #10/98/7651#

    --
    Wayne
    Manchester, England.



    "Bre-x" wrote:

    > Public Function my_dates(the_date As Date) As Integer
    >
    > Select Case the_date
    >
    > Case #7/31/2005# To #8/1/2006#
    > my_dates = 2006
    > Case #7/31/2006# To #8/1/2007#
    > my_dates = 2007
    > Case #7/31/2007# To #8/1/2008#
    > my_dates = 2008
    > Case #7/31/2008# To #8/1/2009#
    > my_dates = 2009
    > Case #7/31/2009# To #8/1/2010#
    > my_dates = 2009
    > Case Else
    > my_date = 0
    > End Select
    > End Function
    >
    > Thank you!!!
    >
    >
    > .
    >
     
    Wayne-I-M, Apr 14, 2010
    #3
  4. "Bre-x" <> wrote in message
    news:...
    > Public Function my_dates(the_date As Date) As Integer
    >
    > Select Case the_date
    > Case Between "07/31/2005" And "08/01/2006"
    > my_dates = 2006
    > case Between "07/31/2006" And "08/01/2007"
    > my_dates = 2006
    > Case Else
    > my_date = 0
    > End Select
    >
    > End Function
    >
    > If try to use # i get the same error
    >
    > Help!!
    >
    > Thank you
    > Bre-x



    Or you could simplify it to something like
    my_Date = Year(DateAdd("d", 153, the_Date))


    John... Visio MVP
     
    John... Visio MVP, Apr 14, 2010
    #4
  5. Bre-x

    Beetle Guest

    How about;

    Public Function my_dates(the_date As Date) As Integer

    If Month(the-date)>7 Then
    my_dates = Year(the_date) + 1
    Else
    my_dates = Year(the_date)
    end if

    End Function

    Then you won't have to keep adding new lines to your code every year

    --
    _________

    Sean Bailey


    "Bre-x" wrote:

    > Public Function my_dates(the_date As Date) As Integer
    >
    > Select Case the_date
    >
    > Case #7/31/2005# To #8/1/2006#
    > my_dates = 2006
    > Case #7/31/2006# To #8/1/2007#
    > my_dates = 2007
    > Case #7/31/2007# To #8/1/2008#
    > my_dates = 2008
    > Case #7/31/2008# To #8/1/2009#
    > my_dates = 2009
    > Case #7/31/2009# To #8/1/2010#
    > my_dates = 2009
    > Case Else
    > my_date = 0
    > End Select
    > End Function
    >
    > Thank you!!!
    >
    >
    > .
    >
     
    Beetle, Apr 14, 2010
    #5
  6. On Wed, 14 Apr 2010 12:47:24 -0600, "Bre-x" <> wrote:

    >Public Function my_dates(the_date As Date) As Integer
    >
    >Select Case the_date
    >
    >Case #7/31/2005# To #8/1/2006#
    > my_dates = 2006
    >Case #7/31/2006# To #8/1/2007#
    > my_dates = 2007
    >Case #7/31/2007# To #8/1/2008#
    > my_dates = 2008
    >Case #7/31/2008# To #8/1/2009#
    > my_dates = 2009
    >Case #7/31/2009# To #8/1/2010#
    > my_dates = 2009
    >Case Else
    > my_date = 0
    >End Select
    >End Function
    >
    >Thank you!!!
    >


    Public Function My_Dates(the_date As Date) As Integer
    My_Dates = Year(DateAdd("m", 5, the_date)
    End Function

    will work in any year (even leap years). And it's one line.
    --

    John W. Vinson [MVP]
     
    John W. Vinson, Apr 15, 2010
    #6
  7. "John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:...
    > On Wed, 14 Apr 2010 12:47:24 -0600, "Bre-x" <> wrote:
    >
    >>Public Function my_dates(the_date As Date) As Integer
    >>
    >>Select Case the_date
    >>
    >>Case #7/31/2005# To #8/1/2006#
    >> my_dates = 2006
    >>Case #7/31/2006# To #8/1/2007#
    >> my_dates = 2007
    >>Case #7/31/2007# To #8/1/2008#
    >> my_dates = 2008
    >>Case #7/31/2008# To #8/1/2009#
    >> my_dates = 2009
    >>Case #7/31/2009# To #8/1/2010#
    >> my_dates = 2009
    >>Case Else
    >> my_date = 0
    >>End Select
    >>End Function
    >>
    >>Thank you!!!
    >>

    >
    > Public Function My_Dates(the_date As Date) As Integer
    > My_Dates = Year(DateAdd("m", 5, the_date)
    > End Function
    >
    > will work in any year (even leap years). And it's one line.
    > --
    >
    > John W. Vinson [MVP]



    A lot more elegant than my solution. You only have to know the number of
    months left in the year rather than the number of days left in the year.

    John... Visio MVP
     
    John... Visio MVP, Apr 15, 2010
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Tom

    Starting And Stopping Within A Selected date Range

    Tom, Jul 24, 2003, in forum: Microsoft Access Getting Started
    Replies:
    1
    Views:
    450
    Howard Brody
    Jul 24, 2003
  2. Samina Gordon

    Populating fields with a date range

    Samina Gordon, Sep 21, 2003, in forum: Microsoft Access Getting Started
    Replies:
    1
    Views:
    162
    PC Datasheet
    Sep 21, 2003
  3. Steve

    Default Date Range

    Steve, Mar 16, 2004, in forum: Microsoft Access Getting Started
    Replies:
    1
    Views:
    121
    Duncan Bachen
    Mar 16, 2004
  4. RichK

    Searches by Date Range

    RichK, Mar 24, 2004, in forum: Microsoft Access Getting Started
    Replies:
    2
    Views:
    116
    eCaVeMaN
    Mar 24, 2004
  5. Al Camp

    Date range parameters

    Al Camp, May 20, 2004, in forum: Microsoft Access Getting Started
    Replies:
    3
    Views:
    141
    Al Camp
    May 20, 2004
Loading...

Share This Page