Expression Builder: Convert STRING to INTEGER for Calculation???

[Guest]

Thank you for your help!

I have three fields within a form: (1) an integer (currency), (2) an inputed
text from a combo box (the text is a payment frequency option: 'annually',
'biannually', 'quarterly', 'monthly'), and (3) an integer in which I wish to
display the result of a calculation taking the the currency integer in (1)
and dividing it by an integer in (2).

EXAMPLE: (1) has a user-inputed integer value of $12,000; (2) has a
user-inputed text value of 'quarterly'. I want (3) to calculate and display
the integer currency $3,000, which is (1) divided by the integer value '4'
(which is derived from the string value 'quarterly').

HOW DO I DO THIS? Can I convert a string value into an assigned integer
value for use in a calculation???

Please help! Thanks for your time!

Sincerely,
Clinton

 

[Nick via AccessMonster.com]

One way to go about it is to use a Variant variable type:

Dim varTimePeriod as Variant

varTimePeriod = YourStringVariable

You can then use varTimePeriod as you would an integer value, which should
work, I played with it a bit. You can also put an error test in there, like:

If Not IsNumeric(varTimePeriod) Then
MsgBox "Please enter a numeric value."
Exit Sub
Else
End if


HTH,
Nick

 

[Dirk Goldgar]

Thank you for your help!

I have three fields within a form: (1) an integer (currency), (2) an
inputed text from a combo box (the text is a payment frequency
option: 'annually', 'biannually', 'quarterly', 'monthly'), and (3) an
integer in which I wish to display the result of a calculation taking
the the currency integer in (1) and dividing it by an integer in (2).

EXAMPLE: (1) has a user-inputed integer value of $12,000; (2) has a
user-inputed text value of 'quarterly'. I want (3) to calculate and
display the integer currency $3,000, which is (1) divided by the
integer value '4' (which is derived from the string value
'quarterly').

HOW DO I DO THIS? Can I convert a string value into an assigned
integer value for use in a calculation???

Please help! Thanks for your time!

Sincerely,
Clinton

Since you say the payment frequency comes from a combo box, the easiest
thing would be to use a hidden column of the combo box to hold the
divisor that goes with the text. Let your combo box get its data from a
table like this:

Table PaymentFrequencies
Field: PymtFreq (Text, primary key)
Field: PymtFreqDivisor (Number/Integer)

Fill the table with these rows:

annually, 1
biannually, 2
quarterly, 4
monthly, 12

Then set your combo box's properties to these:

Row Source: SELECT PymtFreq, PymtFreqDivisor
FROM PaymentFrequencies
ORDER BY PymtFreqDivisor;
Bound Column: 1
Column Count: 2
Column Widths: 1.5", 0"

Now you can set the Control Source property of the third text box to
something like this:

=[BaseAmount] / CInt([PaymentFrequency].[Column](1)

That's assuming "BaseAmount" is the name of your first text box, and
"PaymentFrequency" is the name of the combo box. Make the appropriate
name changes.