Iterate through sequencial variable

  • Thread starter Thread starter QB
  • Start date Start date
Q

QB

I am trying to iterate through a series of sequential variables to use as a
multiplicative factor in a function but can't get the synthax quite right.
Could someone show me the proper way to do the following

For i = 1 To iNoEntries
rs.AddNew
rs![Amount] = Me.[Base Amount] * "Ratio" & i
rs.Update
Next i


if iNoEntries = 3 it would do 3 iterations, the equivalent of:

Me.Amount = Me.[Base Amount] * Ratio1
Me.Amount = Me.[Base Amount] * Ratio2
Me.Amount = Me.[Base Amount] * Ratio3

Thank you

QB
 
Hi,

This is a little unclear. Are Ratio1, Ratio2, ... text boxes on a
form? If so, you could try:

rs![Amount] = Me.[Base Amount] * Me.Controls("Ratio" & i).Value

Clifford Bass
 
Ratio1, Ratio2, ... are all variables in the sub

I use a Select Case statement at the beginning to setup the variable
according to user selectiong on the form.

Select Case Me.Method
Case "1 payment"
iNoEntries = 1
Ratio1 = 1
Case "2 payments"
iNoEntries = 2
Ratio1 = 0.9
Ratio2 = 0.1
End Select

Set rs = Me.frm_Fact.Form.RecordsetClone

For i = 1 To iNoEntries
rs.AddNew
rs![Amount] = Me.[Base Amount] * "Ratio" & i
rs.Update
Next i

rs.close
set rs = Nothing
Me.frm_Fact.Form.Requery


It is simply a question of iterating through the variables defined by the
iNoEntries variable.

QB


JimBurke via AccessMonster.com said:
Like Clifford said, it depends on where those values are coming from. If you
have variables Ratio1, Ratio2, ..., you could create a function GetRatio:

Private Function GetRatio(byval ratioID as integer) as Double (or whatever
data type is needed)

Select Case ratioID
case 1
GetRatio = Ratio1
case 2
GetRatio = Ratio2
... as many case statements as values you have for Ratio...
case Else
do whatever you would do here in case the value isn't valid
End Select

End Function

then in your loop use
rs![Amount] = Me.[Base Amount] * GetRatioAmount(i)

This would depend on how many possible values there are for Ratio - if you a
very large number that method may not be feasible. But this is only if they
are in variables.

If you have a known number of Ratio values and know what their values will
always be, you could create a table tblRatios with fields called ID and
RatioValue, then do a DLookup based on the value of 'i' in your loop, e.g.

rs![Amount] = Me.[Base Amount] * DLookup("RatioValue", "tblRatios",
"RatioID = " & i)
I am trying to iterate through a series of sequential variables to use as a
multiplicative factor in a function but can't get the synthax quite right.
Could someone show me the proper way to do the following

For i = 1 To iNoEntries
rs.AddNew
rs![Amount] = Me.[Base Amount] * "Ratio" & i
rs.Update
Next i

if iNoEntries = 3 it would do 3 iterations, the equivalent of:

Me.Amount = Me.[Base Amount] * Ratio1
Me.Amount = Me.[Base Amount] * Ratio2
Me.Amount = Me.[Base Amount] * Ratio3

Thank you

QB
Click to expand...
Click to expand...
 
Hi,

In which case just use an array with the most entries you will need
(i.e. 10):

Dim Ratio(1 To 10) As Double

Select Case Me.Method
Case "1 payment"
iNoEntries = 1
Ratio(1) = 1
Case "2 payments"
iNoEntries = 2
Ratio(1) = 0.9
Ratio(2) = 0.1
End Select

....

rs![Amount] = Me.[Base Amount] * Ratio (i)


Clifford Bass

QB said:
Ratio1, Ratio2, ... are all variables in the sub

I use a Select Case statement at the beginning to setup the variable
according to user selectiong on the form.

Select Case Me.Method
Case "1 payment"
iNoEntries = 1
Ratio1 = 1
Case "2 payments"
iNoEntries = 2
Ratio1 = 0.9
Ratio2 = 0.1
End Select

Set rs = Me.frm_Fact.Form.RecordsetClone

For i = 1 To iNoEntries
rs.AddNew
rs![Amount] = Me.[Base Amount] * "Ratio" & i
rs.Update
Next i

rs.close
set rs = Nothing
Me.frm_Fact.Form.Requery


It is simply a question of iterating through the variables defined by the
iNoEntries variable.

QB


JimBurke via AccessMonster.com said:
Like Clifford said, it depends on where those values are coming from. If you
have variables Ratio1, Ratio2, ..., you could create a function GetRatio:

Private Function GetRatio(byval ratioID as integer) as Double (or whatever
data type is needed)

Select Case ratioID
case 1
GetRatio = Ratio1
case 2
GetRatio = Ratio2
... as many case statements as values you have for Ratio...
case Else
do whatever you would do here in case the value isn't valid
End Select

End Function

then in your loop use
rs![Amount] = Me.[Base Amount] * GetRatioAmount(i)

This would depend on how many possible values there are for Ratio - if you a
very large number that method may not be feasible. But this is only if they
are in variables.

If you have a known number of Ratio values and know what their values will
always be, you could create a table tblRatios with fields called ID and
RatioValue, then do a DLookup based on the value of 'i' in your loop, e.g.

rs![Amount] = Me.[Base Amount] * DLookup("RatioValue", "tblRatios",
"RatioID = " & i)
I am trying to iterate through a series of sequential variables to use as a
multiplicative factor in a function but can't get the synthax quite right.
Could someone show me the proper way to do the following

For i = 1 To iNoEntries
rs.AddNew
rs![Amount] = Me.[Base Amount] * "Ratio" & i
rs.Update
Next i

if iNoEntries = 3 it would do 3 iterations, the equivalent of:

Me.Amount = Me.[Base Amount] * Ratio1
Me.Amount = Me.[Base Amount] * Ratio2
Me.Amount = Me.[Base Amount] * Ratio3

Thank you

QB
Click to expand...
Click to expand...
Click to expand...
 
I will give it a try! Thank you for the help.

QB




Clifford Bass said:
Hi,

In which case just use an array with the most entries you will need
(i.e. 10):

Dim Ratio(1 To 10) As Double

Select Case Me.Method
Case "1 payment"
iNoEntries = 1
Ratio(1) = 1
Case "2 payments"
iNoEntries = 2
Ratio(1) = 0.9
Ratio(2) = 0.1
End Select

...

rs![Amount] = Me.[Base Amount] * Ratio (i)


Clifford Bass

QB said:
Ratio1, Ratio2, ... are all variables in the sub

I use a Select Case statement at the beginning to setup the variable
according to user selectiong on the form.

Select Case Me.Method
Case "1 payment"
iNoEntries = 1
Ratio1 = 1
Case "2 payments"
iNoEntries = 2
Ratio1 = 0.9
Ratio2 = 0.1
End Select

Set rs = Me.frm_Fact.Form.RecordsetClone

For i = 1 To iNoEntries
rs.AddNew
rs![Amount] = Me.[Base Amount] * "Ratio" & i
rs.Update
Next i

rs.close
set rs = Nothing
Me.frm_Fact.Form.Requery


It is simply a question of iterating through the variables defined by the
iNoEntries variable.

QB


JimBurke via AccessMonster.com said:
Like Clifford said, it depends on where those values are coming from. If you
have variables Ratio1, Ratio2, ..., you could create a function GetRatio:

Private Function GetRatio(byval ratioID as integer) as Double (or whatever
data type is needed)

Select Case ratioID
case 1
GetRatio = Ratio1
case 2
GetRatio = Ratio2
... as many case statements as values you have for Ratio...
case Else
do whatever you would do here in case the value isn't valid
End Select

End Function

then in your loop use
rs![Amount] = Me.[Base Amount] * GetRatioAmount(i)

This would depend on how many possible values there are for Ratio - if you a
very large number that method may not be feasible. But this is only if they
are in variables.

If you have a known number of Ratio values and know what their values will
always be, you could create a table tblRatios with fields called ID and
RatioValue, then do a DLookup based on the value of 'i' in your loop, e.g.

rs![Amount] = Me.[Base Amount] * DLookup("RatioValue", "tblRatios",
"RatioID = " & i)

QB wrote:
I am trying to iterate through a series of sequential variables to use as a
multiplicative factor in a function but can't get the synthax quite right.
Could someone show me the proper way to do the following

For i = 1 To iNoEntries
rs.AddNew
rs![Amount] = Me.[Base Amount] * "Ratio" & i
rs.Update
Next i

if iNoEntries = 3 it would do 3 iterations, the equivalent of:

Me.Amount = Me.[Base Amount] * Ratio1
Me.Amount = Me.[Base Amount] * Ratio2
Me.Amount = Me.[Base Amount] * Ratio3

Thank you

QB
Click to expand...
Click to expand...
Click to expand...
 
Hi,

However, Jim's suggestion of putting the information into a table would
be better. Especially if the ratios might change over time. More on that in
a bit.

tblRatios:

PaymentCount RatioNumber RationValue
1 1 1
2 1 0.9
2 2 0.1

etc.

Then use a DLookup to get the appropriat value; or load the values for
the appropriate payment count into an array and use it.

If the ratios can change over time, add an effective date field:

PaymentCount RatioNumber EffectiveDate RationValue
1 1 01/01/2000 1
2 1 01/01/2000 0.9
2 2 01/01/2000 0.1
2 1 01/01/2010 0.8
2 2 01/01/2010 0.2

And incorporate the use of the effective date in relation to either
today or in relation to a date in a record in order to determine the correct
value(s) to use. If you need more on that let me know.

Clifford Bass
 
Back
Top