DateAdd Function - error when adding one year?

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"))

re: DateAdd Function - error when adding one year?

Hi Kosmos

this works for me
Result: 18/01/2008 18:12:11

What does the 'Date' argument part of your call evaluate to?

HTH

Steve

re: DateAdd Function - error when adding one year?

hmmm....well the code checks to see if the renewal date of a contract is older than or equal to the current date...if so it sets renewal date to the renewal date plus the number of years the contract will renew for. This field is called "TermOfContractInYears" I am running into a problem here whenever this field is equal to 1. The following is the pertinent code:

[PHP] recSet7.Open "tblContracts", con7, adOpenKeyset, adLockOptimistic
recSet7.MoveFirst
Do Until recSet7.EOF
If IsNull(recSet7.Fields("RenewalDate")) And recSet7.Fields("TermOfContractInYears") <> 0 And recSet7.Fields("ContinuousContract") = True Then
Do Until recSet7.Fields("RenewalDate") > Date
If recSet7.Fields("TermOfContractInYears") = 1 Then
recSet7.Fields("RenewalDate") = DateAdd("yyyy", 1, recSet7.Fields("DateofContract"))
recSet7.Fields("RenewalDate") = DateAdd("d", (-1), recSet7.Fields("RenewalDate"))
Else
recSet7.Fields("RenewalDate") = DateAdd("yyyy", recSet7.Fields("TermOfContractInYears"), recSet7.Fields("DateofContract"))
recSet7.Fields("RenewalDate") = DateAdd("d", (-1), recSet7.Fields("RenewalDate"))
End If
Loop
ElseIf Not IsNull(recSet7.Fields("RenewalDate")) And recSet7.Fields("TermOfContractInYears") <> 0 And recSet7.Fields("ContinuousContract") = True Then
If recSet7.Fields("RenewalDate") <= Date Then
Me.txtReference = recSet7.Fields("DatabaseReferenceNumber")
DoCmd.OpenQuery ("UpdateAddedToOutlook")
Do Until recSet7.Fields("RenewalDate") > Date
If recSet7.Fields("TermOfContractInYears") = 1 Then
recSet7.Fields("RenewalDate") = DateAdd("yyyy", 1, recSet7.Fields("DateofContract"))
recSet7.Fields("RenewalDate") = DateAdd("d", (-1), recSet7.Fields("RenewalDate"))
Else
recSet7.Fields("RenewalDate") = DateAdd("yyyy", recSet7.Fields("TermOfContractInYears"), recSet7.Fields("DateofContract"))
recSet7.Fields("RenewalDate") = DateAdd("d", (-1), recSet7.Fields("RenewalDate"))
End If
Loop
End If
End If
recSet7.MoveNext
Loop
recSet7.Close[/PHP]

Whenever TermOfContractInYears is greater than 1, it goes on to the other option and it works which is why I have narrowed down the problem to the number 1 but perhaps it is my code that is causing the problem?

re: DateAdd Function - error when adding one year?

Kosmos

What date is returned when you run ? and the contract term is one year?

re: DateAdd Function - error when adding one year?

I assume you have added in the 1 year contract check to isolate the bug?
Can you give an example of what the DateAdd function returns for a 1-year contract and show the Date of Contract?

Steve

re: DateAdd Function - error when adding one year?

9/7/2005 is the specific date for this contract. Yes I did the if statements to isolate the problem.

re: DateAdd Function - error when adding one year?

Yeah, but what date is the DateAdd function returning for 9/7/2005 ?

re: DateAdd Function - error when adding one year?

nothing it's freezing...sorry...meant to reply to that question as well

re: DateAdd Function - error when adding one year?

This works perfectly: Returns 09/07/2006

Howeever: Returns 30/12/1900 00:00:55

Maybe a matter of formatting the date before using DateAdd?

Steve

re: DateAdd Function - error when adding one year?

hmm well that would certainly make sense considering the loop would never end if it were going back in time. I'll mess around with it and let you know what I come up with. Thanks.

re: DateAdd Function - error when adding one year?

Good luck & keep the thread alive

Steve

re: DateAdd Function - error when adding one year?

Thanks...unfortunately 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.

re: DateAdd Function - error when adding one year?

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

re: DateAdd Function - error when adding one year?

OK, last try!

Just add double-quotes round the date field, thus:- Works here!
Steve

re: DateAdd Function - error when adding one year?

Is Access switching the day and month around?

Steve

re: DateAdd Function - error when adding one year?

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 "tblContracts", con7, adOpenKeyset, adLockOptimistic
recSet7.MoveFirst
Do Until recSet7.EOF
If IsNull(recSet7.Fields("RenewalDate")) And recSet7.Fields("TermOfContractInYears") <> 0 And recSet7.Fields("ContinuousContract") = True Then
recSet7.Fields("RenewalDate") = recSet7.Fields("DateOfContract")
Do Until recSet7.Fields("RenewalDate") > Date
If recSet7.Fields("TermOfContractInYears") = 1 Then
recSet7.Fields("RenewalDate") = DateAdd("yyyy", 1, recSet7.Fields("RenewalDate"))
Else
recSet7.Fields("RenewalDate") = DateAdd("yyyy", recSet7.Fields("TermOfContractInYears"), recSet7.Fields("RenewalDate"))
End If
Loop
recSet7.Fields("RenewalDate") = DateAdd("d", (-1), recSet7.Fields("RenewalDate"))

ElseIf Not IsNull(recSet7.Fields("RenewalDate")) And recSet7.Fields("TermOfContractInYears") <> 0 And recSet7.Fields("ContinuousContract") = True Then
If recSet7.Fields("RenewalDate") <= Date Then
Me.txtReference = recSet7.Fields("DatabaseReferenceNumber")
DoCmd.OpenQuery ("UpdateAddedToOutlook")
Do Until recSet7.Fields("RenewalDate") > Date
If recSet7.Fields("TermOfContractInYears") = 1 Then
recSet7.Fields("RenewalDate") = DateAdd("yyyy", 1, recSet7.Fields("RenewalDate"))
Else
recSet7.Fields("RenewalDate") = DateAdd("yyyy", recSet7.Fields("TermOfContractInYears"), recSet7.Fields("RenewalDate"))
End If
Loop
End If
End If
recSet7.MoveNext
Loop
recSet7.Close[/PHP]

Thanks for all the help!

re: DateAdd Function - error when adding one year?

by the way I tried the tmp thing as well...this did not work..so perhaps the misinterpretation 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"))

??

re: DateAdd Function - error when adding one year?

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

Steve

re: DateAdd Function - error when adding one year?

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.

re: DateAdd Function - error when adding one year?

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

Steve

re: DateAdd Function - error when adding one year?

Yeah, just thought I'd fill in a bit of background there.

People often think that things have some sort of magical status just because they resemble something else (such as a date). But in many cases the programming language will simply interpret it as a numeric expression, or a variable name or whatever.

re: DateAdd Function - error when adding one year?

Yes...but I was using a recSet and pulling my information from a date/time field in the form of a short date. But perhaps that's what happened in the conversion...perhaps Microsoft programmers forgot to keep it as a string :)

re: DateAdd Function - error when adding one year?

Maybe. But I was just referring to the posted example, which used a literal. It wasn't much use an an example, as it had a numeric expression which looked like a date and thus merely confused everyone.

re: DateAdd Function - error when adding one year?

Literal DateTimes and Their Delimiters (#).
(Literal DateTimes and Their Delimiters (#).)
A look through here should explain a lot of these misconceptions.
Important point for this thread - if it is a date field then it doesn't need to be formatted into a string and it doesn't matter what your date formats are either.