469,312 Members | 2,503 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,312 developers. It's quick & easy.

DateAdd Function - error when adding one year?

153 100+
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"))
Jan 18 '07 #1
23 4774
cyberdwarf
218 Expert 100+
Hi Kosmos

this works for me
Expand|Select|Wrap|Line Numbers
  1. DateAdd("yyyy", 1,now)
Result: 18/01/2008 18:12:11

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

HTH

Steve
Jan 18 '07 #2
Kosmos
153 100+
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?
Jan 18 '07 #3
cyberdwarf
218 Expert 100+
Kosmos

What date is returned when you run
Expand|Select|Wrap|Line Numbers
  1. recSet7.Fields("DateofContract")
? and the contract term is one year?
Jan 18 '07 #4
cyberdwarf
218 Expert 100+
I assume you have added in the 1 year contract check to isolate the bug?
Expand|Select|Wrap|Line Numbers
  1.                 If recSet7.Fields("TermOfContractInYears") = 1 Then
  2.                     recSet7.Fields("RenewalDate") = DateAdd("yyyy", 1, recSet7.Fields("DateofContract"))
  3.                     recSet7.Fields("RenewalDate") = DateAdd("d", (-1), recSet7.Fields("RenewalDate"))
  4.                 Else
  5.                     recSet7.Fields("RenewalDate") = DateAdd("yyyy", recSet7.Fields("TermOfContractInYears"), recSet7.Fields("DateofContract"))
  6.                     recSet7.Fields("RenewalDate") = DateAdd("d", (-1), recSet7.Fields("RenewalDate"))
  7.                 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
Jan 18 '07 #5
Kosmos
153 100+
9/7/2005 is the specific date for this contract. Yes I did the if statements to isolate the problem.
Jan 18 '07 #6
cyberdwarf
218 Expert 100+
Yeah, but what date is the DateAdd function returning for 9/7/2005 ?
Jan 18 '07 #7
Kosmos
153 100+
nothing it's freezing...sorry...meant to reply to that question as well
Jan 18 '07 #8
cyberdwarf
218 Expert 100+
This works perfectly:
Expand|Select|Wrap|Line Numbers
  1. DateAdd("yyyy", 1,"09/07/2005"
Returns 09/07/2006

Howeever:
Expand|Select|Wrap|Line Numbers
  1. DateAdd("yyyy", 1,9/7/2005
Returns 30/12/1900 00:00:55

Maybe a matter of formatting the date before using DateAdd?

Steve
Jan 18 '07 #9
Kosmos
153 100+
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.
Jan 18 '07 #10
cyberdwarf
218 Expert 100+
Good luck & keep the thread alive

Steve
Jan 18 '07 #11
Kosmos
153 100+
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.
Jan 18 '07 #12
cyberdwarf
218 Expert 100+
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
Jan 18 '07 #13
cyberdwarf
218 Expert 100+
OK, last try!

Just add double-quotes round the date field, thus:-
Expand|Select|Wrap|Line Numbers
  1. Dim tmp As String
  2. tmp = recSet7.Fields("DateofContract") 
  3. DateAdd("yyyy", 1, tmp)
Works here!
Steve
Jan 18 '07 #14
cyberdwarf
218 Expert 100+
Is Access switching the day and month around?

Steve
Jan 18 '07 #15
Kosmos
153 100+
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!
Jan 18 '07 #16
Kosmos
153 100+
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"))

??
Jan 18 '07 #17
cyberdwarf
218 Expert 100+
Either should work, so long as a valid date is returned by DatAdd

Steve
Jan 18 '07 #18
Killer42
8,435 Expert 8TB
This works perfectly:
Expand|Select|Wrap|Line Numbers
  1. DateAdd("yyyy", 1,"09/07/2005"
Returns 09/07/2006
However:
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 18 '07 #19
cyberdwarf
218 Expert 100+
That's why I suggested using a string...

Steve
Jan 18 '07 #20
Killer42
8,435 Expert 8TB
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.
Jan 18 '07 #21
Kosmos
153 100+
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 :)
Jan 18 '07 #22
Killer42
8,435 Expert 8TB
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.
Jan 18 '07 #23
NeoPa
32,173 Expert Mod 16PB
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.
Jan 19 '07 #24

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Chris Fairall | last post: by
1 post views Thread by Geraldine Hobley | last post: by
6 posts views Thread by Dacuna | last post: by
reply views Thread by =?Utf-8?B?QW5keSBZdQ==?= | last post: by
2 posts views Thread by Chris | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.