Help with Function: date range

[Bre-x]

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]

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]

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

 

[John... Visio MVP]

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

 

[Beetle]

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

 

[John W. Vinson]

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... Visio MVP]

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.

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