DateAdd Function - error when adding one year?

[Kosmos]

I'm using the date add function and for some reason I'm getting an error when I try to add a single year to a date but no error when I try to add more than a single year. can anyone figure out why this may be?

example: DateAdd("yyyy", 1, recSet7.Fields( "DateofContract "))

[cyberdwarf]

Good luck & keep the thread alive

Steve

[Kosmos]

Thanks...unfort unately that doesn't really work. The date will be different each time and so I have to pull it from a table in date format which will be 9/7/2005 and not 09/07/2005. This is confusing.

oh and I also tried it as a text box instead of a date box and did 09/07/2005 but this did not work either.

[cyberdwarf]

OK

You could try to preformat the date into a string. then use the string variable in the function.

It may be that the DateAdd function is misinterpreting the date - I have seen this happen with dates in both Access & SQL functions...

HTH

Steve

[cyberdwarf]

OK, last try!

Just add double-quotes round the date field, thus:-

Dim tmp As String
tmp = recSet7.Fields("DateofContract") 
DateAdd("yyyy", 1, tmp)

Works here!
Steve

[cyberdwarf]

Is Access switching the day and month around?

Steve

[Kosmos]

hmm well perhaps that would work...I found another solution, however, that brings me to believe that perhaps the problem lied in the fact that "DateOfContract " is a required field (not sure why this would cause a problem) but this works as well...


[PHP] recSet7.Open "tblContrac ts", con7, adOpenKeyset, adLockOptimisti c
recSet7.MoveFir st
Do Until recSet7.EOF
If IsNull(recSet7. Fields("Renewal Date")) And recSet7.Fields( "TermOfContract InYears") <> 0 And recSet7.Fields( "ContinuousCont ract") = True Then
recSet7.Fields( "RenewalDat e") = recSet7.Fields( "DateOfContract ")
Do Until recSet7.Fields( "RenewalDat e") > Date
If recSet7.Fields( "TermOfContract InYears") = 1 Then
recSet7.Fields( "RenewalDat e") = DateAdd("yyyy", 1, recSet7.Fields( "RenewalDat e"))
Else
recSet7.Fields( "RenewalDat e") = DateAdd("yyyy", recSet7.Fields( "TermOfContract InYears"), recSet7.Fields( "RenewalDat e"))
End If
Loop
recSet7.Fields( "RenewalDat e") = DateAdd("d", (-1), recSet7.Fields( "RenewalDat e"))

ElseIf Not IsNull(recSet7. Fields("Renewal Date")) And recSet7.Fields( "TermOfContract InYears") <> 0 And recSet7.Fields( "ContinuousCont ract") = True Then
If recSet7.Fields( "RenewalDat e") <= Date Then
Me.txtReference = recSet7.Fields( "DatabaseRefere nceNumber")
DoCmd.OpenQuery ("UpdateAddedTo Outlook")
Do Until recSet7.Fields( "RenewalDat e") > Date
If recSet7.Fields( "TermOfContract InYears") = 1 Then
recSet7.Fields( "RenewalDat e") = DateAdd("yyyy", 1, recSet7.Fields( "RenewalDat e"))
Else
recSet7.Fields( "RenewalDat e") = DateAdd("yyyy", recSet7.Fields( "TermOfContract InYears"), recSet7.Fields( "RenewalDat e"))
End If
Loop
End If
End If
recSet7.MoveNex t
Loop
recSet7.Close[/PHP]

Thanks for all the help!

[Kosmos]

by the way I tried the tmp thing as well...this did not work..so perhaps the misinterpretati on is because I am doing

recSet1.Fields( "A") = DateAdd("yyyy", #, recSet1.Fields( "B"))

maybe you can only do

recSet1.Fields( "A") = DateAdd("yyyy", #, recSet1.Fields( "A"))

??

[cyberdwarf]

Either should work, so long as a valid date is returned by DatAdd

Steve

[Killer42]

This works perfectly:

DateAdd("yyyy", 1,"09/07/2005"

Returns 09/07/2006
However:

DateAdd("yyyy", 1,9/7/2005

Returns 30/12/1900 00:00:55

I expect that's because in the second case you're using a numeric expression which would evaluate to around .00064125. That is, 9 divided by 7 (about 1.3) divided by 2,005. This very small number is presumably interpreted, in a date/time context, as about 55 seconds after midnight on the "first" day.

[cyberdwarf]

That's why I suggested using a string...

Steve