A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help with Function: date range



 
 
Thread Tools Display Modes
  #1  
Old April 14th, 2010, 07:37 PM posted to microsoft.public.access.gettingstarted
Bre-x
external usenet poster
 
Posts: 103
Default 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  
Old April 14th, 2010, 07:47 PM posted to microsoft.public.access.gettingstarted
Bre-x
external usenet poster
 
Posts: 103
Default 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  
Old April 14th, 2010, 09:07 PM posted to microsoft.public.access.gettingstarted
Wayne-I-M
external usenet poster
 
Posts: 3,674
Default 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  
Old April 14th, 2010, 10:35 PM posted to microsoft.public.access.gettingstarted
John... Visio MVP
external usenet poster
 
Posts: 900
Default 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  
Old April 14th, 2010, 11:18 PM posted to microsoft.public.access.gettingstarted
Beetle
external usenet poster
 
Posts: 1,254
Default 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  
Old April 15th, 2010, 12:03 AM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old April 15th, 2010, 12:41 AM posted to microsoft.public.access.gettingstarted
John... Visio MVP
external usenet poster
 
Posts: 900
Default 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  
Old April 15th, 2010, 12:23 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:17 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.