Solved

between dates and list months

Posted on 2003-11-27
34
1,376 Views
Last Modified: 2007-12-19
Here is the problem:  Date opened account is a date field.   Date closed account is a date field.  I earned interest every month.  If I opened the account 05/20/2003 my interest earned started 6/20/2003. (kind of like a cd) I closed my account on 12/20/2003.  I earned a total of $700.  I earned this over the 7 months I had the account. So how do I show individual months(name) with the $100 per month.... June 100, July 100 August 100.

My tables have Date opened Date closed and Total interest...
0
Comment
Question by:TimKestermont
  • 22
  • 11
34 Comments
 
LVL 18

Expert Comment

by:1William
Comment Utility
Because the interest is paid based on days rather  than months, you need to calculate it based on the day number vs the month number.   It is also dependant on the type of interset compoundng.  Is it yearly, monthly or daily compounding?
Now.... If you are not caluclating the interest, but are entering in a amount, it gets easier.  You first use the date diff function to calculate the number of days between your start and end date.  Then, divide the amount by the result of the date diff calculation.  Now you have a amount per day.  You next step will be to determine how many days between the start date and the end of that month.  Multiply this value by the daily amount.  and so on!
0
 
LVL 7

Expert Comment

by:wsteegmans
Comment Utility
Possible function you could use: InterestEarned.
It has three parameters:
- StartDate
- CloseDate
- Interest per month

You can call it like this:
InterestEarned(#06/20/2003#,#12/20/2003#,100)

The code:

Public Function InterestEarned(StartDate As Date, CloseDate As Date, InterestPerMonth As Integer) As Integer

' - Count number of months
    bytmonths = DateDiff("m", StartDate, CloseDate)
   
' - If the month is not complete, substract one month
    If Day(StartDate) > Day(CloseDate) Then
        bytmonths = bytmonths - 1
    End If

' - Compute Interest
    InterestEarned = bytmonths * InterestPerMonth
   
End Function
0
 

Author Comment

by:TimKestermont
Comment Utility
I will try your solution.  I was only asked to do it by the month.  eg...6/30/2003 would reflect the first month of interest would be 7/30/2003 of say $100 would display July $100

Thanks ...I will try it after turkey!  Happy Thanksgiving my friend
0
 

Author Comment

by:TimKestermont
Comment Utility
These aren't a go.... Once again    
Dateopend 6/20/2003        DateClosed12/20/2003    $600 earnedinterest

Because the first month interest is actually 7/20/2003  I need it to reflect this whole line like this.   July   Aug   Sept   Oct  Nov  Dec      
              100    100   100    100 100  100  
0
 
LVL 7

Expert Comment

by:wsteegmans
Comment Utility
So, this line

July   Aug   Sept   Oct  Nov  Dec
100   100   100    100  100  100  

You wan't to store it in your DB, show it in a form or MessageBox?

I think the function already works, because when you run this:
InterestEarned(#6/20/2003#,#12/20/2003#,100)
it returns 600. So, that's OK?

So, explain in more detail what you wants ...
0
 

Author Comment

by:TimKestermont
Comment Utility
I apologize then because perhaps I'm missing something.  Did I need to put the bytmonths as interger in the public function?  And I want to show it in a form.  You been a great help

Public Function InterestEarned(Cd_Issue_Date As Date, Cd_Maturity_Date As Date, bytmonths As Integer, InterestPerMonth As Integer) As Integer

' - Count number of months
    bytmonths = DateDiff("m", "Cd_Issue_Date", "Cd_Maturity_Date ")
   
' - If the month is not complete, substract one month
    If Day("Cd_Issue_Date") > Day("Cd_Maturity_Date ") Then
        bytmonths = bytmonths - 1
    End If

' - Compute Interest
    InterestEarned = bytmonths * InterestPerMonth

End Function
   
0
 

Author Comment

by:TimKestermont
Comment Utility
My table is field Dateopend 6/20/2003       field DateClosed12/20/2003     field$600 earnedinterest and I don't know...like I said I could be wrong but I don't see it converting the date into the actual month as in July etc...  You are probably right but maybe you give me alittle more detail.

Thanks

0
 
LVL 7

Accepted Solution

by:
wsteegmans earned 400 total points
Comment Utility
Hi,

> Did I need to put the bytmonths as interger in the public function
No, it's just a variable. I forgot to declare it (shame on me!)

I changed the code a bit, and created a sample application. Maybe we can create this sample application together. So, you can have a look and if you like it, you can copy/paste the code and some other things in your real application.

If you want some more, just ask. But it's easy then, because we're talking about the same application (sample/demo).

OK, I do it step by step ..

- Create a new Access Application
- Create a new Module in Access and paste the code of our Public InterestEarned Function
    Public Function InterestEarned(ByVal Cd_Issue_Date As Date, ByVal Cd_Maturity_Date As Date, _
           ByVal InterestPerMonth As Integer, ByRef InterestMonths As String) As Integer
   
        Dim bytMonths As Byte
        Dim intCounter As Integer
        Dim datTemp As Date
   
    ' - Count number of months
        bytMonths = DateDiff("m", Cd_Issue_Date, Cd_Maturity_Date)
       
    ' - If the month is not complete, substract one month
        If Day(Cd_Issue_Date) > Day(Cd_Maturity_Date) Then
            bytMonths = bytMonths - 1
        End If
   
    ' - Compute Interest
        InterestEarned = bytMonths * InterestPerMonth
       
    ' - Lookup all months you earned interest (starting with the next month after you opened)
        datTemp = DateAdd("m", 1, Cd_Issue_Date)
        Do While datTemp <= Cd_Maturity_Date
            InterestMonths = InterestMonths & Format(datTemp, "mmmm yyyy") & ";100;"
            datTemp = DateAdd("m", 1, datTemp)
        Loop
       
    ' - We remove the last seperation character (;)
        If Right(InterestMonths, 1) = ";" Then InterestMonths = Left(InterestMonths, Len(InterestMonths) - 1)
       
    End Function
- Save the module and close it
- Now, create a new form.
- On this form create these fields with the given names:
      Command Button       Name: cmdComputeInterest
      Text Box        Name: txtOpenDate
      Text Box        Name: txtCloseDate
      Text Box        Name: txtTotal
      List Box        Name: lstInterest
            Column Count: 2
- Select the On Click Event of the Command Button, and choose Code builder
- Paste this code in the Command Button On Click Event:
    Private Sub cmdComputeInterest_Click()
        Dim lngInterestEarned As Long
        Dim strInterestMonths As String
       
    ' - First, compute our interest. The new parameter (strInterestMonths) will be used
    ' - to store all the months we earned some interest
        lngInterestEarned = InterestEarned(Me.txtOpenDate, Me.txtCloseDate, 100, strInterestMonths)
       
    ' - Store the total in the total field on the form (txtTotal)
        Me.txtTotal.Value = CStr(lngInterestEarned)
       
    ' - Fill the List Box with an overview of all the months we earned some interest.
        Me.lstInterest.RowSourceType = "Value List"
        Me.lstInterest.RowSource = strInterestMonths
       
    End Sub
- Run this form. Fill in the OpenDate and CloseDate textboxes like for example this:
      OpenDate: 06/20/2002
      CloseDate: 12/20/2003
- As you can see, our application show the total, show all the months we earned some interest

As you also can see, it can handle multiple years! That's because I included the year, ...

Regards!
0
 

Author Comment

by:TimKestermont
Comment Utility
Working late on Thanksgiving eh?  Well thanks for this information..I will get on it here in an hour or so...I anxious to check it out!

Thanks again for walking me through this

Tim
0
 

Author Comment

by:TimKestermont
Comment Utility
Hey my friend, I am getting a type mismatch error on line

lngInterestEarned = InterestEarned(Me.txtOpenDate, Me.txtCloseDate, 100, strInterestMonths)

Also my form is coming up with #name?....I'm looking at the code and at the properties window and it appears to respresent the text boxes correctly.

Tim,
0
 
LVL 7

Expert Comment

by:wsteegmans
Comment Utility
What version of Access do you use (it's created and tested with Access 2000)

Try to change this line
lngInterestEarned = InterestEarned(Me.txtOpenDate, Me.txtCloseDate, 100, strInterestMonths)
to
lngInterestEarned = InterestEarned(CDate(Me.txtOpenDate), CDate(Me.txtCloseDate), 100, strInterestMonths)

BTW
> Working late on Thanksgiving eh?
From Belgium, so no Thanksgiving for us ;-)

Regards,
Wouter
0
 

Author Comment

by:TimKestermont
Comment Utility
Also the  Me.txtTotal.Value = CStr(lngInterestEarned) shows error on txt.total.  I see the current form field txtTotal in your code but I'm not following the .Value part.  If you have made this code work for you then I must have something wrong. I copied everything.

I apologize,  I think I'm alittle more confused.  I still have my table  Cd_issue_date and Cd_maturity_date  and InterestEarned.

So what the heck am I doing that isn't going through properly?
0
 

Author Comment

by:TimKestermont
Comment Utility
I'm using Access 2000 also.
0
 

Author Comment

by:TimKestermont
Comment Utility
One more thing- what do you mean: ' - Fill the List Box with an overview of all the months we earned some interest.
        Me.lstInterest.RowSourceType = "Value List"
        Me.lstInterest.RowSource = strInterestMonths

0
 

Author Comment

by:TimKestermont
Comment Utility
<<<<<<<<Try to change this line>>>>>> THIS WORKED BUT STILL STUCK ON THE txtTotal.Value

lngInterestEarned = InterestEarned(Me.txtOpenDate, Me.txtCloseDate, 100, strInterestMonths)
to
lngInterestEarned = InterestEarned(CDate(Me.txtOpenDate), CDate(Me.txtCloseDate), 100, strInterestMonths

WOW!!  I'm mental on this one!!!
0
 
LVL 7

Expert Comment

by:wsteegmans
Comment Utility
> I see the current form field txtTotal in your code but I'm not following the .Value part.
In fact, this line
Me.txtTotal.Value = CStr(lngInterestEarned)
is the same as this line
Me.txtTotal = CStr(lngInterestEarned)

The second is without .Value. Because, the property .Value of your TextBox is the default property. So, if you don't write a property (like our second case), the .Value is automatically used.

> Me.lstInterest.RowSourceType = "Value List"
> Me.lstInterest.RowSource = strInterestMonths
What I do, I fill this List Box with all the months you earned interest. So, the content of this List Box will be something like:
February 2003      100
March 2003          100
...

The first line says 'RowSourceType = "Value List", it means that we will fill this List Box with a list of values. You can also fill a List Box with some data from a table or query (just look in the help, there is some more info, if you want)
The second line defines the content of our List Box (RowSource). If everything works, the variable strInterestMonths is holding something like "February 2003;100;March 2003;100;..." The semicolon (;) is our delimiter. Because we set the List Box to 2 columns, two fields (February 2003;100) is always one row.

You will see when it it's working ...

So, only this one is a giving us some trouble:
Me.txtTotal.Value = CStr(lngInterestEarned)

Try to use a Variant (not the best option) but to test, so change it into
Me.txtTotal.Value = CVar(lngInterestEarned)
or try without value, like
Me.txtTotal = CStr(lngInterestEarned)

Regards!
0
 
LVL 7

Expert Comment

by:wsteegmans
Comment Utility
What is the error you get?
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:TimKestermont
Comment Utility
I got the dates entered in the OPEN and CLOSE Dates in the form but when I click the cmdcomputeinterest button I get type mismatch regardless of the line I put in

""""Try to change this line
lngInterestEarned = InterestEarned(Me.txtOpenDate, Me.txtCloseDate, 100, strInterestMonths)
to
lngInterestEarned = InterestEarned(CDate(Me.txtOpenDate), CDate(Me.txtCloseDate), 100, strInterestMonths)"""""""
0
 
LVL 7

Expert Comment

by:wsteegmans
Comment Utility
Very strange ...

It would be easy if I could have a look at your DB. So, if you want, mail it to me. You can find my email on the bottom of my profile -> concatinate the two strings with an AT (@)

I will take a look at it ...

Regards!
0
 

Author Comment

by:TimKestermont
Comment Utility
That would be great really.  I just made a simple one up for this purpose.  If I ' out the ingInterest after the Interest Earned of course it puts the number in fine.  I will send it to you
0
 

Author Comment

by:TimKestermont
Comment Utility
Thanks for letting me do this.  I really appreciate your efforts.  

Tim
0
 
LVL 7

Expert Comment

by:wsteegmans
Comment Utility
So, that was a difficult one ..

What's going wrong ...

You also created a table (Table1) with these fields:
- Cd_Issue_Date
- Cd_Maturity_Date
- Interestearned

What's now the funny part, your last field has exactly the same name as my function !! That's why we get the TypeMismatch error. He was using the Field instead of the function ...

So, to get this thing to work, first remove this Table1 as Record Source (leave it empty). Then run it ...

We can later eventually bound the Text Box Controls to a database table. But, run it first without any table ...

Regards!
0
 

Author Comment

by:TimKestermont
Comment Utility
Thanks...I'm doing it now!
0
 

Author Comment

by:TimKestermont
Comment Utility
Did you get the code to work?  I put dates in the open field and the closed field and nothing.. ???  I've copied your code exactly...I put the form up with the txtopendate txtclosed etc...  I'm not getting it...I guess.... any explanations?  If you got the code to work for you then it's on ;my end...but I wouldn't know what I'm doing wrong?

Tim
0
 

Author Comment

by:TimKestermont
Comment Utility
Here is something else I'm not getting- are we populating the list box with a result or a manual entry?   And above you said this "- Run this form. Fill in the OpenDate and CloseDate textboxes like for example this:
     OpenDate: 06/20/2002
     CloseDate: 12/20/2003
- As you can see, our application show the total, show all the months we earned some interest

My question is how do you get the interest to show up?  Where are you pulling that information if you only put dates in?  I can read VBA pretty well but I think this has to be above my head.  So what do you say?  Call it quits?  
0
 

Author Comment

by:TimKestermont
Comment Utility
If you are still in then I am...Just don't want to burn you out

Good days Tim
0
 
LVL 7

Expert Comment

by:wsteegmans
Comment Utility
Hi Tim,

> Did you get the code to work?  I put dates in the open field and the closed field and nothing.. ???
After filling in the two dates, you must press on the Compute-button, but I think you did? Then it will process the two dates and show the interest earned.

Try this first ... if we have it really running, I explain all the code ...

So, the previous DB (e-mailed), remove the property Record Source of your Form. It containted Table1, but we do it first without any tables ... So, make this property empty, fill in the two dates and press the compute button?

Does this work?

Regards!
0
 

Author Comment

by:TimKestermont
Comment Utility
I'll start new again...I'm having a 4 day weekend and I'm here working....this has got to work...Regards to you my friend
0
 

Author Comment

by:TimKestermont
Comment Utility
I have a form and  a table- nothing is bound (no more interestearned field-renamed it so thats no longer an issue) If I open the form I have 3 empty text fields and 1 empty list box and one compute button.  If I put in 2 dates a click compute nothing happens.


It seems to me though that defining a date field as a txt field it won't recognize the date as a date.
 Hey I think we can do this!
Are you in California?  Go Chiefs!!! I'm in KC---
0
 
LVL 7

Expert Comment

by:wsteegmans
Comment Utility
Open the code for your Compute Button. So, there is some code in your Click-Event.
Set a breakpoint on the first real line, this line:
lngInterestEarned = Interestearned(Me.txtOpenDate, Me.txtClosedate, 100, strInterestMonths)

So, when you press the compute button, Access stops on your breakpoint. No, go step by step (using F8) through the code and look what happens...

Also, did you saved everything? If you don't save your module and form (code) it won't work ...

Does this work, this Breakpoint thing?

Regards!
0
 

Author Comment

by:TimKestermont
Comment Utility
have you made the code work?

Tim
0
 

Author Comment

by:TimKestermont
Comment Utility
Lets start from scratch...I've tried/ or you can send back the DB with the working code you have used.

What I have is a table with the following 3 fields
Dateopend 6/20/2003        DateClosed12/20/2003    $600 earnedinterest

My report has to show that 30days after the dateopened we  earnedinterest.

Step 1 is the 30 days calc- even though it was opened in 06/20/2003 should reflect 07/20/2003.
Step 2 is change date into string or txt for the actual month.
Step 3 is to calculate the division for  600 over the 6 months.  The result is the following:

july   Aug   Sept   Oct  Nov  Dec
100   100   100    100  100  100  

Lets do it step by step.  Make each work on its own then we can put it together.

Tim...Go Chiefs!!

0
 
LVL 7

Expert Comment

by:wsteegmans
Comment Utility
OK, let's do it step by step:

> Step 1 is the 30 days calc- even though it was opened in 06/20/2003 should reflect 07/20/2003.
This is done in the function 'InterestEarned' by this code:

' - Count number of months
    bytMonths = DateDiff("m", Cd_Issue_Date, Cd_Maturity_Date)
   
' - If the month is not complete, substract one month
    If Day(Cd_Issue_Date) > Day(Cd_Maturity_Date) Then
        bytMonths = bytMonths - 1
    End If

I do it a bit different. first I do a DataDiff between the Ussue_Date and the Maturity_Date. So, the DateDiff for 06/20/2003 and 12/20/2003 gives => 6 (months).

The second part checks if the day of the Issue_Date is bigger then the Maturity_Date. Example: 06/20/2003 and 12/18/2003. As you can see, the last month isn't complete because we don't have a complete month (just until the 18th). So, we substract one month of the total (means 6 - 1 = 5)

> Step 2 is change date into string or txt for the actual month.
This can you do with the Format Function, like this:
Format(datTemp, "mmmm yyyy")
This give us for example "July 2003" or "October 2003"

> Step 3 is to calculate the division for  600 over the 6 months.
I did it a bit different. I thought it's always 100 interest a month. But, now we must calculate it ourselves ...

So, let's create a new test database (step by step)

- Create a new Access DB
- Create a new table with name tblInterestEarned and these fields:
      -> Cd_Issue_Date (Date/Time)
      -> Cd_Maturity_Date (Date/time)
      -> Interestearned (Number)
- Create a new module
- Copy/Paste the public function CalculateInterest

 Public Function CalculateInterest(ByVal Cd_Issue_Date As Date, ByVal Cd_Maturity_Date As Date, _
           ByVal TotalInterestEarned As Integer) As String
   
        Dim bytMonths As Byte
        Dim intCounter As Integer
        Dim datTemp As Date
        Dim strInterestMonths As String
        Dim sngInterestPerMonth As Single
   
    ' - Count number of months
        bytMonths = DateDiff("m", Cd_Issue_Date, Cd_Maturity_Date)
       
    ' - If the month is not complete, substract one month
        If Day(Cd_Issue_Date) > Day(Cd_Maturity_Date) Then
            bytMonths = bytMonths - 1
        End If
   
    ' - Compute Interest for each month
        sngInterestPerMonth = CSng(TotalInterestEarned / bytMonths)
       
    ' - Lookup all months you earned interest (starting with the next month after you opened)
        datTemp = DateAdd("m", 1, Cd_Issue_Date)
        Do While datTemp <= Cd_Maturity_Date
    ' - - - strInterestMonth holds every month (full name) and the interest we earned that month
            strInterestMonths = strInterestMonths & Format(datTemp, "mmmm yyyy") & ";" & _
                                Format(sngInterestPerMonth, "#.00") & ";"
            datTemp = DateAdd("m", 1, datTemp)
        Loop
       
    ' - We remove the last seperation character (;)
        If Right(strInterestMonths, 1) = ";" Then strInterestMonths = Left(strInterestMonths, Len(strInterestMonths) - 1)
       
        CalculateInterest = strInterestMonths
               
    End Function

- Save it and close it
- Create a new form and call it frmInterestEarned
- As RecordSource, use our new created table tblInterestEarned
- Drop the three fields of our table on the form, so we have these fields:
           Text Box             Name: Cd_Issue_Date      
           Text Box             Name: Cd_Issue_Date
           Text Box             Name: Interestearned
- Drop to extra control
      List Box      Name: lstInterestEarned
                  Column Count: 2
      Command Button      Name: cmdComputeInterest
- OK, pick the On click event of the Command button, and choose Code builder:
- Paste this code

        Dim lngInterestEarned As Long
        Dim strInterestMonths As String
       
    ' - First, compute our interest. The new parameter (strInterestMonths) will be used
    ' - to store all the months we earned some interest
        strInterestMonths = CalculateInterest(Me.Cd_Issue_Date, Me.Cd_Maturity_Date, Me.Interestearned)
       
    ' - Fill the List Box with an overview of all the months we earned some interest.
        Me.lstInterest.RowSourceType = "Value List"
        Me.lstInterest.RowSource = strInterestMonths

- OK. Save all your stuff.
- Fill some data in the new tblInterestEarned.
- Open your new form, scroll throught the records, and push the Compute-button if you want to calculate the interest (all months with interest for each month)

Regards!
0
 

Author Comment

by:TimKestermont
Comment Utility
Thanks wsteegmans  More than excellent my friend!

This takes the date and changes it to month and year(06/21/2003) to July yes "July" 2003 because your first month completed would be July.
Then takes a given start date and end date with total money at the end date, divides the amount by the months and produces "how much money total" "how much money per month was earned and list the month year and amount" and can calculate the figure for you through a command button.
Pretty incredible!  

Private Sub Form_Current()
    Dim lngInterestEarned As Long
    Dim strInterestMonths As String
           
    If Not Me.NewRecord Then
       
    ' - First, compute our interest. The new parameter (strInterestMonths) will be used
    ' - to store all the months we earned some interest
        strInterestMonths = CalculateInterest(Me.Cd_Issue_Date, Me.Cd_Maturity_Date, Me.Cd_Interest_Return)
       
    ' - Fill the List Box with an overview of all the months we earned some interest.
        Me.lstInterest.RowSourceType = "Value List"
        Me.lstInterest.RowSource = strInterestMonths
   
    Else
        Me.lstInterest.RowSource = ""
    End If


Public Function CalculateInterest(ByVal Cd_Issue_Date As Date, ByVal Cd_Maturity_Date As Date, _
           ByVal TotalInterestEarned As Currency) As String
   
        Dim bytMonths As Byte
        Dim intCounter As Integer
        Dim datTemp As Date
        Dim strInterestMonths As String
        Dim curInterestPerMonth As Currency
   
    ' - Count number of months
        bytMonths = DateDiff("m", Cd_Issue_Date, Cd_Maturity_Date)
       
    ' - If the month is not complete, substract one month
        If Day(Cd_Issue_Date) > Day(Cd_Maturity_Date) Then
            bytMonths = bytMonths - 1
        End If
   
    ' - Compute Interest for each month
        curInterestPerMonth = CSng(TotalInterestEarned / bytMonths)
       
    ' - Lookup all months you earned interest (starting with the next month after you opened)
        datTemp = DateAdd("m", 1, Cd_Issue_Date)
        Do While datTemp <= Cd_Maturity_Date
    ' - - - strInterestMonth holds every month (full name) and the interest we earned that month
            strInterestMonths = strInterestMonths & Format(datTemp, "mmmm yyyy") & ";" & _
                                Format(curInterestPerMonth, "#.00") & ";"
            datTemp = DateAdd("m", 1, datTemp)
        Loop
       
    ' - We remove the last seperation character (;)
        If Right(strInterestMonths, 1) = ";" Then strInterestMonths = Left(strInterestMonths, Len(strInterestMonths) - 1)
       
        CalculateInterest = strInterestMonths
               
    End Function

And a REGARDS TO WS
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now