DateBucket Function

[JCShort]

I'm a beginner (at best) to writing code. I have a "Completion_Date" field in my form. However my work months are not based on calendar months, so I want to populate another field with the work month based on the date that was entered in the Completion_Date.

I'm using Access 2003. Can anyone tell me why the following code doesn't work?

Public Function DateBucket(Completion_Date As Date) As String
Select Case Completion_Date
Case Is <= #12/20/2007#
DateBucket = "Prior_Year - " & Completion_Date
Case Is <= #1/25/2008# And Completion_Date >= #12/21/2007#
DateBucket = "January 2008"
Case Is <= #2/22/2008# And Completion_Date >= #1/26/2008#
DateBucket = "February 2008"
Case Is <= #3/21/2008# And Completion_Date >= #2/23/2008#
DateBucket = "March 2008"
Case Is <= #4/25/2008# And Completion_Date >= #3/22/2008#
DateBucket = "April 2008"
Case Is <= #5/23/2008# And Completion_Date >= #4/26/2008#
DateBucket = "May 2008"
Case Is <= #6/20/2008# And Completion_Date >= #5/24/2008#
DateBucket = "June 2008"
Case Is <= #7/25/2008# And Completion_Date >= #6/21/2008#
DateBucket = "July 2008"
Case Is <= #8/22/2008# And Completion_Date >= #7/26/2008#
DateBucket = "August 2008"
Case Is <= #9/19/2008# And Completion_Date >= #8/23/2008#
DateBucket = "September 2008"
Case Is <= #10/24/2008# And Completion_Date >= #9/20/2008#
DateBucket = "October 2008"
Case Is <= #11/21/2008# And Completion_Date >= #10/25/2008#
DateBucket = "November 2008"
Case Is <= #12/26/2008# And Completion_Date >= #11/22/2008#
DateBucket = "December 2008"
Case Else
DateBucket = "Future_Year - " & Completion_Date
End Select
End Function

Thanks in advance!

 

[nIGHTmAYOR]

Your function is working fine here , would u post the syntax u run ur function with ?

 

[pbaldy]

Personally I would be very hesitant to code something that will change next year and require code changes. I'd probably create a lookup table with from and to dates, and the resulting text value. Then you can join that table to others to find the value, or use DLookup. With a table, you can provide a form for your users to maintain the data, so no code changes are required.

 

[CyberLynx]

I also found the code to work.

What exactly is it not doing that you want it to do?

I agree with pbaldy as towards hard-coding dates. You will be forced to change the code every year. An alternative perhaps may be to allow the function to accept a optional Year to base from, or pull the Year from the Current Date. Like this:

Public Function DateBucket(Completion_Date As Date, _
                           Optional ByVal WrkYear As Integer = 0) As String
   Dim YR As Integer
   If WrkYear = 0 Then YR = Year(Now) Else YR = WrkYear
   
   Select Case Completion_Date
      Case Is <= CDate("12/20/" & (YR - 1))
         DateBucket = "Prior_Year - " & Completion_Date
      Case Is <= CDate("1/25/" & YR) And Completion_Date >= CDate("12/21/" & (YR - 1))
         DateBucket = "January " & YR
      Case Is <= CDate("2/22/" & YR) And Completion_Date >= CDate("1/26/" & YR)
         DateBucket = "February " & YR
      Case Is <= CDate("3/21/" & YR) And Completion_Date >= CDate("2/23/" & YR)
         DateBucket = "March " & YR
      Case Is <= CDate("4/25/" & YR) And Completion_Date >= CDate("3/22/" & YR)
         DateBucket = "April " & YR
      Case Is <= CDate("5/23/" & YR) And Completion_Date >= CDate("4/26/" & YR)
         DateBucket = "May " & YR
      Case Is <= CDate("6/20/" & YR) And Completion_Date >= CDate("5/24/" & YR)
         DateBucket = "June " & YR
      Case Is <= CDate("7/25/" & YR) And Completion_Date >= CDate("6/21/" & YR)
         DateBucket = "July " & YR
      Case Is <= CDate("8/22/" & YR) And Completion_Date >= CDate("7/26/" & YR)
         DateBucket = "August " & YR
      Case Is <= CDate("9/19/" & YR) And Completion_Date >= CDate("8/23/" & YR)
         DateBucket = "September " & YR
      Case Is <= CDate("10/24/" & YR) And Completion_Date >= CDate("9/20/" & YR)
         DateBucket = "October " & YR
      Case Is <= CDate("11/21/" & YR) And Completion_Date >= CDate("10/25/" & YR)
         DateBucket = "November " & YR
      Case Is <= CDate("12/26/" & YR) And Completion_Date >= CDate("11/22/" & YR)
         DateBucket = "December " & YR
      Case Else
         DateBucket = "Future_Year - " & Completion_Date
   End Select
End Function

.

 

[JCShort]

Thanks all! I got it to work. As it turns out, I named my Module "DateBucket". Once I changed the name of the module to "Functions", it worked fine...with the exception of Null values.

I had to create a separate field with: IIf(IsDate([Completion_Date])=-1, DateBucket([Completion_Date]), "Not Complete"). Then I was able to pull the text from this field into my table. I don't know if there was a better work-around, but this did the trick.

Unfortunately, there isn't any rhyme or reason to the dates, they are literally picked off of a calendar. The only similarity is that they are all Fridays. So, they have to be hardcoded at the beginning of each year.

Thanks again!