Re: Access Date Hell

"shurstgbr" <simon.hurst@xxxxxxxxxxxxxxxxx> wrote in message
news:1129219301.553023.70350@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Sorry all i should have been clearer (and there was also a mistake in
> my post!). I want to add years onto my original date and these can be
> between 0 and 7.5 with multiple decimals (although to be truthful I'm
> not sure how accurately I want the date!). The point I was trying to
> get across that 09/07/04 plus 4*365.2425 (i.e 4 years) gives 08/07/08
> and not the 09/07/08 as any right minded person would expect. I am only
> adding fractions of years so i suppose 1 month would be 1/12. Hope that
> helps


Obviously dates are a funny business. How many days are there in half a
year? (you have to ask: which year?) How many days in month (you have to
ask: which month?). As Allen has pointed out, you would be much better off
adding a fixed number of days, months or years to any given date - as nobody
disputes what the date will be in, say, 489 days time.
Not that I would recommend this approach, but just to remind you that you
could define your function however you liked. For example you could do it
as shown below which does a great job of adding exactly 7 years to a date
but less so if you try to add 1/12 of a year when you really wanted to add a
calendar month.


Public Function AddYears(TheDate As Date, NoOfYears As Double) As Date
' Not really a useful suggestion
Dim dteTemp As Date
Dim lngDays As Long

dteTemp = TheDate

' Remove any time portion of the date
dteTemp = CDate(Fix(dteTemp))

' Add the whole number of years
If CLng(Fix(NoOfYears)) <> 0 Then
dteTemp = DateAdd("yyyy", CLng(Fix(NoOfYears)), dteTemp)
End If

' Find the number of days in final year
lngDays = CLng(DateSerial(Year(dteTemp), 12, 31) -
DateSerial(Year(dteTemp), 1, 1))

' Find the whole number of days in this year that elapse
lngDays = CLng(Fix(lngDays * (NoOfYears - Fix(NoOfYears))))

' Add these days to the result
dteTemp = DateAdd("d", lngDays, dteTemp)

AddYears = dteTemp

End Function


.

Relevant Pages

  • RE: Date Minues Month
    ... Dim dteTemp As Date ... "Rob Wills" wrote: ... "Thorson" wrote: ...
    (microsoft.public.access.queries)
  • Days in month
    ... Not a built in, but the UDF is pretty simple: ... Dim dteTemp As Date ...
    (microsoft.public.access.gettingstarted)