Anthony
asked on
Not receiving correct result from vba code in access 2010
Hi All,
I wrote the below code to calculate an extra day after a term date has been given for an individual. So if a term date is entered of 12/11/13, I need the result to be 12/12/13. With this, I've had to modify the code to for months that have 31, 30, and 28 days (we don't count leap year). I've tried the code out with someone have the termination date of 11/30/13, hoping that it would produce 20131201 but instead it just gives me 20131130. I'm hoping someone could help me understand why the coding isn't working. To further understand the code, the ELDENT field is formatted in "CYYMMDD" which is why I add it to 19000000 to get a YYYYMMDD.
I wrote the below code to calculate an extra day after a term date has been given for an individual. So if a term date is entered of 12/11/13, I need the result to be 12/12/13. With this, I've had to modify the code to for months that have 31, 30, and 28 days (we don't count leap year). I've tried the code out with someone have the termination date of 11/30/13, hoping that it would produce 20131201 but instead it just gives me 20131130. I'm hoping someone could help me understand why the coding isn't working. To further understand the code, the ELDENT field is formatted in "CYYMMDD" which is why I add it to 19000000 to get a YYYYMMDD.
Public Function fnMbrTerm(ELDENT As Double, ELDETS As Double, RunEnd As Double)
Dim TermCalc As Double
If ((ELDENT > 0) And (ELDETS = 0) And (ELDENT < RunEnd)) Then
TermCalc = ELDENT + 19000000
End If
If Right(TermCalc, 4) = 1231 Then
fnMbrTerm = TermCalc + 8870
ElseIf Right(TermCalc, 4) = ("0131" Or "0331" Or "0531" Or "0731" Or "0831" Or "1031") Then
fnMbrTerm = TermCalc + 70
ElseIf Right(TermCalc, 4) = ("0430" Or "0630" Or "0930" Or "1130") Then
fnMbrTerm = TermCalc + 71
ElseIf Right(TermCalc, 4) = ("0228") Then
fnMbrTerm = TermCalc + 73
Else
fnMbrTerm = TermCalc
End If
If (ELDENT = 0) Then
fnMbrTerm = "00000000"
End If
End Function
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jim,
Thanks for the response. I like how concise your formula is, but I need to enter some other variables to ensure that I don't continue to pickup the term. For example, by having that ELDETS = 0 ensures that it's the first time I'm picking up the term. After my run, that field is populated with a value and then it is bypassed on the next run the following month.
Thanks for the response. I like how concise your formula is, but I need to enter some other variables to ensure that I don't continue to pickup the term. For example, by having that ELDETS = 0 ensures that it's the first time I'm picking up the term. After my run, that field is populated with a value and then it is bypassed on the next run the following month.
ASKER
The weird thing is I was able to figure it out by some adjustment, but I get a weird result when I need the year to change...look at this line of code:
ElseIf Right(TermCalc, 4) = 1231 Then
fnMbrTerm = TermCalc + 8870
All of the other logic applies above. If you actually do the computation for an ELDENT = 1131231 and then add 19000000 you get 20131231 (the TermCalc result from above). The four right numbers are 1231. So I'm saying that the function equal the TermCalc result of 20131231 + 8870 which when you actually add it up equal 20140101 which is the correct answer. Instead, I'm getting 20131301. Have any clue why this might be happening?
ElseIf Right(TermCalc, 4) = 1231 Then
fnMbrTerm = TermCalc + 8870
All of the other logic applies above. If you actually do the computation for an ELDENT = 1131231 and then add 19000000 you get 20131231 (the TermCalc result from above). The four right numbers are 1231. So I'm saying that the function equal the TermCalc result of 20131231 + 8870 which when you actually add it up equal 20140101 which is the correct answer. Instead, I'm getting 20131301. Have any clue why this might be happening?
ASKER
Jim, I was able to get yours to work great. Thank you all for your help.
ASKER
The first one was great reading on learning how to do such a task of splitting the task. The second solution was an actual resolve to the problem.
ASKER
Thank you for this. I was also able to figure it out another way. I realized that when I completed the Right function, that the four positions were not really true because of the leading zero was dropped. When I scaled back the reference to just the right 3 values it worked like a charm. I realized that I would never have the same right 3 values a calendar because of the way the months land.
Thank you for this, though. I will try to incorporate it into my next phase of the project.
-Ant