Construct Projected Date

[Dan2kx]

Hello to all,

what i would like to do is have a bit of VB code to set a variable to the next tax year but am having trouble constructing it..

Taxdate = 01/04/year+1

so if this year 2008 then i want the taxt year to return the first monday around 01/04/2009
taking into account that if today was after new year the next taxdate would be the same... any ideas???

thanks for the interest

[patjones]

Hello to all,

what i would like to do is have a bit of VB code to set a variable to the next tax year but am having trouble constructing it..

Taxdate = 01/04/year+1

so if this year 2008 then i want the taxt year to return the first monday around 01/04/2009
taking into account that if today was after new year the next taxdate would be the same... any ideas???

thanks for the interest

Look in the Access help pages for the DateAdd function. It has three arguments: the time interval you are working with (e.g. days, months, etc.), how many of those intervals you want to add, and the date you want to add to.

For instance, to add 25 days to May 14th, 2008, you could write DateAdd("d", 25, "14-May-2008"). For you, the only twist is that the number of intervals would have to be calculated depending on what the current date is, which you could probably use DateDiff for...

Pat

[Dan2kx]

Its unfortunately the twist that i cant wrap my head around...

i would have to work out the date before i could work out the difference to work out the date, doesnt make sense does it is it possible to do something like this


if month(date) between 04 and12 then x = 1 else x = 0
taxdate = day(01)+month(0 4)+year(x)

[patjones]

Its unfortunately the twist that i cant wrap my head around...

i would have to work out the date before i could work out the difference to work out the date, doesnt make sense does it is it possible to do something like this


if month(date) between 04 and12 then x = 1 else x = 0
taxdate = day(01)+month(0 4)+year(x)

OK, try this:

 Dim intCurrentMonth As Integer 
Dim intCurrentYear As Integer
Dim intNextYear As Integer
 
Dim intNumOfDaysToAdd As Integer
 
Dim dteTaxDate As Date
 
intCurrentMonth = DatePart("m", Now)
intCurrentYear = DatePart("yyyy", Now)
 
intNextYear = intCurrentYear+1
 
If intCurrentMonth >=4 And intCurrentMonth <=12 Then
 
	 intNumOfDaysToAdd = DateDiff("d", Now, "1-April-" & intNextYear)	 
 
Else
 
	 intNumOfDaysToAdd = DateDiff("d", Now, "1-April-" & intCurrentYear)
 
EndIf
 
dteTaxDate = DateAdd("d", intNumOfDaysToAdd, Now)

This is actually quick and dirty, because I'm using the VB canned date functions, but it seems to work with the examples I tried out in my Immediate Window...

Pat

[Dan2kx]

genius that seems to work thanks!!

[patjones]

genius that seems to work thanks!!

It's no problem. And one other thing...when I ran it in my Immediate Window, I found that it returned dteTaxDate with the time of day part also. You might want to take action to get rid of that, depending on what your need is.

Pat

[Dan2kx]

substituting "now" for "date" solved that problem

thanks again

[NeoPa]

=CDate("1 April " & Year(Date())+IIf(Month(Date())<4,0,1))

[patjones]

=CDate("1 April " & Year(Date())+IIf(Month(Date())<4,0,1))

Now I'm eating my words "quick and dirty"...

[NeoPa]

Not so fast Pat.

My solution doesn't find the Monday, it just finds the 1st of the month.

[patjones]

Not so fast Pat.

My solution doesn't find the Monday, it just finds the 1st of the month.

And neither does mine! Yikes...

This would find the first Monday after the tax date:

 Dim dteTaxDate As Date 
 
dteTaxDate=CDate("1 April " & Year(Date())+IIf(Month(Date())<4,0,1))
 
while DatePart("w",dteTaxDate) <> vbMonday
	 dteTaxDate = DateAdd("d", 1, dteTaxDate)
wend

Pat

[NeoPa]

You can use the Weekday() function arithmetically to go straight to the correct date value :

Public Function TaxDate() As Date
  TaxDate = CDate("1 April " & Year(Date()) + IIf(Month(Date()) < 4, 0, 1))
  TaxDate = TaxDate + 7 - Weekday(TaxDate, vbTuesday)
End Function

PS. The reason this is difficult (more complicated) in SQL is that the 1st April calculation would need to appear twice.

[patjones]

And neither does mine! Yikes...

This would find the first Monday after the tax date:

 Dim dteTaxDate As Date 
 
dteTaxDate=CDate("1 April " & Year(Date())+IIf(Month(Date())<4,0,1))
 
while DatePart("w",dteTaxDate) <> vbMonday
	 dteTaxDate = DateAdd("d", 1, dteTaxDate)
wend

Pat

I tried this out with several dates and it seems to work nicely, finding the nearest Monday to April 1st...

 Dim dteTaxDate As Date 
Dim a As Integer
 
dteTaxDate = CDate("1 April " & Year(#12/6/2007#) + IIf(Month(#12/6/2007#) < 4, 0, 1))
 
a = DatePart("w", dteTaxDate, vbMonday) - 1
 
dteTaxDate = IIf(a <= 3, DateAdd("d", -a, dteTaxDate), DateAdd("d", 7 - a, dteTaxDate))

Pat

[FishVal]

Hello, gentlemen.

It seems to me that the logic has a bug.
What will happen if current date is Sunday, 2 April?

[NeoPa]

Then you get Monday 2nd April (2007).

I tested it (my code) for Sunday 2nd April 2006.

PS. Did you mean gentlemen? Or were you talking only of Pat's code?