If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with Function: date range
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 |
#2
|
|||
|
|||
Help with Function: date range
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!!! |
#3
|
|||
|
|||
Help with Function: date range
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!!! . |
#4
|
|||
|
|||
Help with Function: date range
"Bre-x" wrote in message
... 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 |
#5
|
|||
|
|||
Help with Function: date range
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!!! . |
#6
|
|||
|
|||
Help with Function: date range
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] |
#7
|
|||
|
|||
Help with Function: date range
"John W. Vinson" wrote in message
... 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 |
#8
|
|||
|
|||
Help with Function: date range
Your function seems rather inconsistent. Firstly the date ranges overlap on
31 July/1 August; secondly it returns the same value for the two years 31 July 2008 to 1 August 2010. Did you mean to return 2010 in the last case? For greater flexibility the following returns the accounting year in a format such as 2009-10 by passing the date value, and the day and month when the accounting year starts into the function: Public Function AcctYear(DateVal As Date, MonthStart As Integer, DayStart As Integer) As String Dim dtmYearStart As Date ' get start of accounting year in year of date value dtmYearStart = DateSerial(Year(DateVal), MonthStart, DayStart) ' if date value is before start of accounting year ' accounting year starts year previous to date's year, ' otherwise it starts with date's year If DateVal dtmYearStart Then AcctYear = Year(DateVal) - 1 & Format(Year(DateVal) Mod 100, "-00") Else AcctYear = Year(DateVal) & Format((Year(DateVal) + 1) Mod 100, "-00") End If End Function For the UK fiscal year traditionally starting 5 April for instance: AcctYear([TheDate], 4, 5) In your case it would be: AcctYear([TheDate], 8, 1) I'm assuming your year starts 1 August. To return just the ending year you'd amend it to: If DateVal dtmYearStart Then AcctYear = Year(DateVal) Else AcctYear = Year(DateVal) + 1 End If and make the data type of the return value an integer number rather than a string. Ken Sheridan Stafford, 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!!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201004/1 |
Thread Tools | |
Display Modes | |
|
|