?
Solved

Not receiving correct result from vba code in access 2010

Posted on 2013-12-17
7
Medium Priority
?
590 Views
Last Modified: 2013-12-17
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.

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

Open in new window

0
Comment
Question by:Anthony6890
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
7 Comments
 
LVL 10

Assisted Solution

by:Korbus
Korbus earned 400 total points
ID: 39725599
If you can extract the day, month and year- just create a DateTime object in your function.  Once you do that, adding a day is super easy (no worries about leap years, days in a month, etc...), so is extracting out the day month and year again.  Looks like this page can give details on how to do that:  http://msdn.microsoft.com/en-us/library/aa227564(v=vs.60).aspx
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 39725602
This is great reading.

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
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1600 total points
ID: 39725618
Give this a try:

Public Function fnMbrTerm(ELDENT As Double) As String

Dim strFormatted As String

If (ELDENT = 0) Then
  fnMbrTerm = "00000000"
Else
   ELDENT = ELDENT + 19000000
   strFormatted = CStr(ELDENT)
   fnMbrTerm = Format$(DateSerial(Mid(strFormatted, 1, 4), Mid(strFormatted, 5, 2), Mid(strFormatted, 7, 2)) + 1, "YYYYMMDD")
End If


End Function


Jim.
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 1

Author Comment

by:Anthony6890
ID: 39725623
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.
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 39725663
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?
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 39725691
Jim, I was able to get yours to work great.  Thank you all for your help.
0
 
LVL 1

Author Closing Comment

by:Anthony6890
ID: 39725695
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.
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question