Numeric calculations in a text box

[Malcolm P]

I have a form, Expenses, which updates a table, Expenses.
On the form is a text box which updates the table's field
ExpAmt. Currently, using the form, when I input to this
text box I can only enter a number: ie, 12.50 or 1.25.
I would like to be able to enter a series of numbers and
have Access total the series and use the sum as the number
which updates the table: ie, refering to the above.......
input 1.25 + 10.00 + .25 + 1.00 and have the sum, 12.50,
update the table.

 

[GVaught]

It doesn't work that way. In order to sum values that may appear in an
Expense form, place the textbox in the form's footer section. In an unbound
text box in the Control source section (Properties of textbox) you can type
=Sum([fieldname1]+[fieldname2]+[fieldname3] etc. This way if any of the
values change in the expense report you don't have to recreate the 1.25 +
10.00 etc. If you do it your way, it will cause untold inaccuracies.

 

[Malcolm P]

Thanks for the reply.
I am aware of the method that you suggested, but I must
have not been clear in my posting. Let me give another
example:
On the form I have a one textbox into which I input an
amount. This amount that I input comes from pieces of
paper that I am given. These pieces of paper sometimes
have two or more amounts on them that I have to add on an
adding machine: ie, 1.25 + 10.00 + .25 + 1.00 = 12.50. I
then take the total, 12.50, and input into the form. What
I want to do in the form's textbox is have it function as
the adding machine, so that I can skip using the adding
machine. I want to be able to click onto the textbox and
enter the following keystrokes 1.25 + 10.00 + .25 + 1.00
{enter}, have it total the series of numbers and give me a
sum which it then uses as the entry and updates the
related table.
Not part of this Access program I am trying to use..... I
use an accounting software package called QuickBooks
(which is prehaps one of the most used small business
accounting programs in the country). When I input to
QuickBooks it allows me to do what I am trying to do in
Access.
I hope the above clarifies what I am trying to do.

-----Original Message-----
It doesn't work that way. In order to sum values that may appear in an
Expense form, place the textbox in the form's footer section. In an unbound
text box in the Control source section (Properties of textbox) you can type
=Sum([fieldname1]+[fieldname2]+[fieldname3] etc. This way if any of the
values change in the expense report you don't have to recreate the 1.25 +
10.00 etc. If you do it your way, it will cause untold inaccuracies.

--
G Vaught

I have a form, Expenses, which updates a table, Expenses.
On the form is a text box which updates the table's field
ExpAmt. Currently, using the form, when I input to this
text box I can only enter a number: ie, 12.50 or 1.25.
I would like to be able to enter a series of numbers and
have Access total the series and use the sum as the number
which updates the table: ie, refering to the above.......
input 1.25 + 10.00 + .25 + 1.00 and have the sum, 12.50,
update the table.

.

 

[Steve]

Try this function in the BeforeUpdate or AfterUpdate event of the text box.
It checks where the + signs are in the string, extracts the numeric part and
then adds the numbers together.

Public Function AddExpenses(ByVal strExp As String) As Double

Dim exp As Double
Dim v As Long
Dim x As Long
Dim y As Long

If InStr(1, strExp, "+") > 0 Then
v = 1
y = 1
exp = 0
Do While InStr(v, strExp, "+") > 0
x = InStr(v, strExp, "+")
exp = exp + CDbl(Mid(strExp, v, x - v))
v = x + 1
y = y + 1
Loop
exp = exp + CDbl(Mid(strExp, v, Len(strExp) - x))
Else
exp = CDbl(strExp)
End If

AddExpenses = exp

End Function

Thanks for the reply.
I am aware of the method that you suggested, but I must
have not been clear in my posting. Let me give another
example:
On the form I have a one textbox into which I input an
amount. This amount that I input comes from pieces of
paper that I am given. These pieces of paper sometimes
have two or more amounts on them that I have to add on an
adding machine: ie, 1.25 + 10.00 + .25 + 1.00 = 12.50. I
then take the total, 12.50, and input into the form. What
I want to do in the form's textbox is have it function as
the adding machine, so that I can skip using the adding
machine. I want to be able to click onto the textbox and
enter the following keystrokes 1.25 + 10.00 + .25 + 1.00
{enter}, have it total the series of numbers and give me a
sum which it then uses as the entry and updates the
related table.
Not part of this Access program I am trying to use..... I
use an accounting software package called QuickBooks
(which is prehaps one of the most used small business
accounting programs in the country). When I input to
QuickBooks it allows me to do what I am trying to do in
Access.
I hope the above clarifies what I am trying to do.

-----Original Message-----
It doesn't work that way. In order to sum values that may appear in an
Expense form, place the textbox in the form's footer section. In an unbound
text box in the Control source section (Properties of textbox) you can type
=Sum([fieldname1]+[fieldname2]+[fieldname3] etc. This way if any of the
values change in the expense report you don't have to recreate the 1.25 +
10.00 etc. If you do it your way, it will cause untold inaccuracies.

--
G Vaught

I have a form, Expenses, which updates a table, Expenses.
On the form is a text box which updates the table's field
ExpAmt. Currently, using the form, when I input to this
text box I can only enter a number: ie, 12.50 or 1.25.
I would like to be able to enter a series of numbers and
have Access total the series and use the sum as the number
which updates the table: ie, refering to the above.......
input 1.25 + 10.00 + .25 + 1.00 and have the sum, 12.50,
update the table.

.