PC Review


Reply
Thread Tools Rate Thread

Help with Function: date range

 
 
Bre-x
Guest
Posts: n/a
 
      14th Apr 2010
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


 
Reply With Quote
 
 
 
 
Bre-x
Guest
Posts: n/a
 
      14th Apr 2010
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!!!


 
Reply With Quote
 
 
 
 
Wayne-I-M
Guest
Posts: n/a
 
      14th Apr 2010
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!!!
>
>
> .
>

 
Reply With Quote
 
John... Visio MVP
Guest
Posts: n/a
 
      14th Apr 2010
"Bre-x" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

 
Reply With Quote
 
Beetle
Guest
Posts: n/a
 
      14th Apr 2010
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!!!
>
>
> .
>

 
Reply With Quote
 
John W. Vinson
Guest
Posts: n/a
 
      15th Apr 2010
On Wed, 14 Apr 2010 12:47:24 -0600, "Bre-x" <(E-Mail Removed)> 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]
 
Reply With Quote
 
John... Visio MVP
Guest
Posts: n/a
 
      15th Apr 2010
"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:(E-Mail Removed)...
> On Wed, 14 Apr 2010 12:47:24 -0600, "Bre-x" <(E-Mail Removed)> 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

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do Count a the number of times a date range appears within a date range!? leelondon Microsoft Excel Discussion 5 4th Oct 2006 12:12 PM
Link date to a table with Date Range (Eff Date and term date) =?Utf-8?B?S2V2aW4gUmVlZA==?= Microsoft Access 1 31st Aug 2006 04:23 PM
i have two date fileds Opend date Due date, can i set default on due date so, its always = to open date on my data entry form1 Urgent Mike Saifie Microsoft Access Form Coding 1 9th Mar 2006 02:08 AM
Query Date Range Criteria Doesn't Include Last Date in Range Karl Burrows Microsoft Access Queries 6 10th Jun 2005 07:24 AM
Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! -$- Windows XP Internet Explorer 2 21st Dec 2003 11:45 PM


Features
 

Advertising
 

Newsgroups
 


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