Not to Show Certain Dates ..?

[Les]

Can anyone please help me with my current dillema.
I'm creating a billing form and i need to breakdown the monthly payments (date). I have a field where users enter the first payment [firstpayment] and another field where they enter the number of months [MonthsToPay]. Now, I breakdown the months by:
Select DateAdd("m",0,[firstpayment]) AS Payd1,
DateAdd("m",1,[firstpayment]) AS Payd2,
DateAdd("m",2,[firstpayment]) AS Payd3,
DateAdd("m",3,[firstpayment]) AS Payd4, 'etc...

Now what i need to do is to only show the dates within the first payment date and the number of months to pay [MonthsToPay].

Please please please help!
I'm also open for suggestion.

Les

 

[Les]

i tried to use CASE but it doesn't seem to work on access.
please help

 

[Les]

Had to use IIf() on the textboxes in the form.

=IIf([MonthsToPay] > 0, [pay1],"")

Gee, thanks all.

 

[raskew]

The following is not the precise answer to your dilemma, but if you'll copy/paste the code to a new module and then type (in the debug window)
? myppmt <enter>, you may find the results interesting and it'll hopefully provide food for thought. Caution--when asked for number of months, specify <= 30! One of the ugly things about MsgBoxs is that it's possible to make them expand to the point where you are unable to exit the routine.

Function MyPPmt()
Dim NL, TB, fmt, FVal, PVal, APR, TotPmts, PayType, Payment, Msg, _
MakeChart, Period, P, i, Bal
Const ENDPERIOD = 0, BEGINPERIOD = 1    ' When payments are made.
NL = Chr(13) & Chr(10)  ' Define newline.
TB = Chr(9) ' Define tab.
fmt = "###,###,##0.00"  ' Define money format.
FVal = 0    ' Usually 0 for a loan.
PVal = InputBox("How much do you want to borrow?")
APR = InputBox("What is the annual percentage rate of your loan?")
If APR > 1 Then APR = APR / 100 ' Ensure proper form.

TotPmts = InputBox("How many monthly payments do you have to make?")
PayType = MsgBox("Do you make payments at the end of month?", vbYesNo)
If PayType = vbNo Then PayType = BEGINPERIOD Else PayType = ENDPERIOD
Payment = Abs(-Pmt(APR / 12, TotPmts, PVal, FVal, PayType))
Msg = "Your monthly payment is " & Format(Payment, fmt) & ". "
Msg = Msg & "Would you like a breakdown of your principal and "
Msg = Msg & "interest per period?"
MakeChart = MsgBox(Msg, vbYesNo)    ' See if chart is desired.

If MakeChart <> vbNo Then
    Msg = "Month  Payment  Principal  Interest   Balance" & NL
    Bal = PVal
    Period = 1
    Do While Bal > Payment
      P = PPmt(APR / 12, Period, TotPmts, -PVal, FVal, PayType)
      P = (Int((P + 0.005) * 100) / 100)  ' Round principal.
      Bal = Bal - P
      i = Payment - P
      i = (Int((i + 0.005) * 100) / 100)  ' Round interest.
      Msg = Msg & Period & TB & Format(Payment, fmt)
      Msg = Msg & TB & Format(P, fmt) & TB & Format(i, fmt) & TB & Format(Bal, fmt) & NL
      Period = Period + 1
    Loop
    Msg = Msg & Period & TB & Format(Bal, fmt)
    Msg = Msg & TB & Format(Bal, fmt) & TB & Format(0, fmt) & TB & Format(0, fmt) & NL
    
    'Next Period
    MsgBox Msg, vbInformation, "Start amount = " & PVal
    ' Display amortization table.
End If
End Function

 

[Les]

Raskew,

Your reply looks interesting. Can you give a step-by-step instruction to an obvious rookie on how to see this work.

Attaching a file would help.

 

[Les]

i am using access 2000 and typing
? whatever
on the debug window comesback only with
"print whatever"

 

[raskew]

(1) Highlight everything from Function ... to End Function.
(2) Copy it to the clipboard with <Ctrl-C>
(3) From the Access Database window <Alt-F1> click on the Modules tab and then on New.
(4) Paste the copied code into the module with <Ctrl-V>.
(5) Go to the Debug window <Ctrl-G> and type:
? MyPPmt <Enter>

That's all there is to it. Successive MsgBoxes will open, prompting you to enter details. Just follow the prompts, enter OK when presented an option and it'll do the work for you.

 

[Les]

Ctrl-G .. got it.

Very impressive raskew.

 

[Les]

definitely a food for thought. now, I have to find out whether they want the loan to have a fixed or annual rate. A new dillemma. lol

thanks,
les