DATEPART CONDITIONAL CALCULATIONS.....AAAAAGGGGH!!!! 1

[kimmole]

i need to work out a figure based on a [start date] and the [5th april] each year....

so my start date may be 12/01/2002.... how many days till the next 5th april? =x
or my start may be......01/01/2003.... how many days till the next 5th april? =x
or my start may be......01/06/2003.... how many days till the next 5th april? ie 2004 =x

the resultant figure will get used like this............(([value]/365)*[x])/100)*[field y]...
if however the item has gone past 5th april once then...(([value]/365)*[x])/100)*[field z]...


so i guess i need a ...iif(datpart("yyyy"[start date].......comparison type thingy to work this out!!!!!

if this makes any sence to anyone...please help i'm going nuts.....
dates calculations just burn me up!

 

[jebry]

Hi!

Try the following:

Dim dtNextApril As Date
Dim intNumberOfDays As Integer

If Month([StartDate]) > 4 Or (Month([StartDate]) = 4 And Day([StartDate]) > 5) Then
dtNextApril = DateSerial(Year([StartDate]) + 1, 4, 5)
Else
dtNextApril = DateSerial(Year([StartDate]), 4, 5)
End If

intNumberOfDays = DateDiff("d", [StartDate], dtNextApril)


hth
Jeff Bridgham
bridgham@purdue.edu