between dates and list months

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...
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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
Commented:
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 Commented:
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 Commented:
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
Commented:
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 Commented:
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 Commented:
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
Commented:
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)
Do While datTemp <= Cd_Maturity_Date
InterestMonths = InterestMonths & Format(datTemp, "mmmm yyyy") & ";100;"
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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
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 Commented:
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
Commented:
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 Commented:
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 Commented:
I'm using Access 2000 also.
0
Author Commented:
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 Commented:
<<<<<<<<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
Commented:
> 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
Commented:
What is the error you get?
0
Author Commented:
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
Commented:
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 Commented:
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 Commented:
Thanks for letting me do this.  I really appreciate your efforts.

Tim
0
Commented:
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 Commented:
Thanks...I'm doing it now!
0
Author Commented:
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 Commented:
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 Commented:
If you are still in then I am...Just don't want to burn you out

Good days Tim
0
Commented:
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 Commented:
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 Commented:
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
Commented:
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 Commented:
have you made the code work?

Tim
0
Author Commented:
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
Commented:
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)
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") & ";"
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 Commented:
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)
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") & ";"
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.