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]

Hi Kosmos

this works for me

DateAdd("yyyy", 1,now)

Result: 18/01/2008 18:12:11

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

HTH

Steve

[Kosmos]

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?

[cyberdwarf]

Kosmos

What date is returned when you run

recSet7.Fields("DateofContract")

? and the contract term is one year?

[cyberdwarf]

I assume you have added in the 1 year contract check to isolate the bug?

                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

Can you give an example of what the DateAdd function returns for a 1-year contract and show the Date of Contract?

Steve

[Kosmos]

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

[cyberdwarf]

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

[Kosmos]

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

[cyberdwarf]

This works perfectly:

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

Returns 09/07/2006

Howeever:

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

Returns 30/12/1900 00:00:55

Maybe a matter of formatting the date before using DateAdd?

Steve

[Kosmos]

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.

[cyberdwarf]

Good luck & keep the thread alive

Steve

[Kosmos]

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.

[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 "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!

[Kosmos]

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

??

[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

[Killer42]

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

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.

[Kosmos]

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

[Killer42]

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

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.

[NeoPa]

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.