Goal Seek

[Kathy Hume]

Does anyone know if there is the equivalent in access to goal seek in excel?

 

[gemma-the-husky]

its hard to understand how it would/could apply

the point about access (unlike excel) is that any row of data is independent of other rows, and there IS NO implied relationship that would make an implicit goal seek meaningful, imo.

However, you can always code specific functionality - so can you clarify what you are trying to achieve?

 

[Kathy Hume]

I am designing a database to record and calculate mortgages. To calculate the payment the PMT function is used using three fields for the caculation.Amount, interest rate and term. What I want to achieve is, what would be the result of one field by changing another field. eg by changing the payment what would the new interest rate be

In excel you can you use goal seek to achieve this

 

[stopher]

I attach an example.

Here's the code for convenience:

Dim intRate As Integer
Dim intNper As Integer
Dim intPV As Integer
Dim intPMT As Integer
Dim intTOT As Integer

[COLOR="Red"]'Use 2^n numbering system to code
'which values are present[/COLOR]
If IsNumeric(Me.MonthlyRate) Then intRate = 8
If IsNumeric(Me.Nper_Months) Then intNper = 4
If IsNumeric(Me.PVal) Then intPV = 2
If IsNumeric(Me.Payment) Then intPMT = 1
intTOT = intRate + intNper + intPV + intPMT

[COLOR="red"]'use the code to determine which calculation to perform[/COLOR]
Select Case intTOT
    Case 7      'calc Rate
        Me.MonthlyRate = Rate(Me.Nper_Months, -Me.Payment, Me.PVal)
    Case 11     'calc Nper
        Me.Nper_Months = NPer(Me.MonthlyRate, -Me.Payment, Me.PVal)
    Case 13     'calc PVal
        Me.PVal = PV(Me.MonthlyRate, Me.Nper_Months, -Me.Payment)
    Case 14     'calc Payment
        Me.Payment = PMT(Me.MonthlyRate, Me.Nper_Months, -Me.PVal)
    Case 15     'too many entries
        MsgBox "Remove data from value to be calculated"
End Select

hth
Chris

 

[Kathy Hume]

Thanks Chris

I think i need more training to be able to use the 'code'.