How to calculate form field


Please any body know how to design a form with calculation on a field.
How can I format the table field to get result of form field.

Acturaly what I want is , when I ender numeric data to form field e.g
I want to sum 5 data (125.50+200+100+45+75) for one field without
calculate separately
Are there any format to enter these as excel cell to get result of
545.50?.

Like this

= 125.50+200+100+45+75)

This really depends if you are summing inside a single column, or across
multiple columns. If it is across multiple columns (fields) then you
will have to write the summing either directly in the SQL, or calculate
is locally in the forms code once the data is available.

If you are summing inside a single column of data then the SQL Sum()
function should give you what you want.
--
Cheers

The Frog

"Lanka Danweem" <mail (AT) lankadanweempituwa (DOT) com> wrote

This question leads me to believe you are asking a question about Microsoft
Excel, not about Microsoft Access. They are different software products,
and although the VBA language is used in both, the object model and
structure are quite different. The comp.databases.ms-access newsgroup is for
technical discussion of, and questions and answers about, Microsoft Access.
If you are asking about Excel, you would have a higher probability of
getting the answer you need in a newsgroup devoted to Excel.

If you are, in fact, asking about Microsoft Access, based on the information
you've given, my answer would be this:

Access Forms do not have Fields, they have Controls. Forms may be "bound"
to a Table or Query, specified in the Form's Record Source property,
Controls may be bound to a Field from the Record Source which is specified
in the Control's Control Source Property, or may be Calculated Controls
according to an Expression in the Conrtol's Control Source, but cannot be
both bound and calculated. What you can do is to copy the contents of the
Calculated Control to a Control bound to a Field before the Record is
written.

I recommend that you "Requery" the Calculated Control whenever there is a
change to any factor (e.g., another Control) and at that time, copy its
content to the bound Control (which need not be visible).

Another approach is to put the calculation in VBA code that you execute each
time there's a change to any factor, and put the result directly in the
Control bound to the Field where you want that information stored -- that
way, you have no need for a Calculated Control but accomplish the same
purpose.

Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Lanka Danweem" <mail (AT) lankadanweempituwa (DOT) com> wrote

On Aug 11, 2:05*am, Lanka Danweem <m... (AT) lankadanweempituwa (DOT) com> wrote:
You can create a text box on a form. If you open the property sheet
up for the text box and give it a name of Sumit by pressing on the
Other tab. Then click on the Data tab and select ControlSource and
enter:
=125.50+200+100+45+75

If you have field names
=YourFieldName1+YourFieldName2+YourFieldName3...

You can now refer to it in code.
msgbox "Sum is " & Me.Sumit

On Aug 11, 9:48*pm, "Access Developer" <accde... (AT) gmail (DOT) com> wrote:
Thank you very much Mr willey, yes i want to know about MS access not
Excel. when I search the help , found it can be done with spread sheet
control. but I have no idea about that.

Thanks

On Aug 11, 11:15*pm, Patrick Finucane <patrickfinucan... (AT) gmail (DOT) com>
wrote:
Thank, yes I'm doing it same. but I thought it may can do like MS
excel.

On Aug 11, 6:10*pm, The Frog <mr.frog.to.... (AT) googlemail (DOT) com> wrote:
Dear Mr Frog ,
Could you kindly give me little more details about SQL Sum(). Thanks
for your reply
Bandara

On Aug 12, 1:36*am, Lanka Danweem <m... (AT) lankadanweempituwa (DOT) com> wrote:

To different products; You could try this. Create a 2 text boxes;
Text0 and Text1. In the controlsource of Text0 enter
=Eval([Text1])
Now in Text1 you can enter 1 + 2 + 3 and the result will show up in
the other text box.

On 12/08/2011 07:36:11, Lanka Danweem wrote:
You could try this bit of code in an AfterUpdate of a unbound field called
Text0

Private Sub Text0_AfterUpdate()

Dim Stg As String, Letter As String
Dim Tot As Single
Dim i As Integer, j As Integer

If Left(Text0, 1) <> "=" Then Exit Sub

Stg = Right(Text0, Len(Text0) - 1) ' Lose the "="

For i = 1 To Len(Stg)
Letter = Mid(Stg, i, 1)
If Letter <> "0" _
And Letter <> "1" _
And Letter <> "2" _
And Letter <> "3" _
And Letter <> "4" _
And Letter <> "5" _
And Letter <> "6" _
And Letter <> "7" _
And Letter <> "8" _
And Letter <> "9" _
And Letter <> "." _
And Letter <> "+" Then
MsgBox "This is not a simple sum", vbInformation
Exit Sub
End If
Next i

i = 0
NextFigure:
i = InStr(i + 1, Stg, "+") ' First "+"
If i = 0 Then ' no "+" single figure
Tot = Tot + CSng(Mid(Stg, j + 1, Len(Stg) - j))
GoTo PrintIt
Else
Tot = Tot + CSng(Mid(Stg, j + 1, i - j - 1))
End If
j = i
GoTo NextFigure
PrintIt:
Text0 = Tot

End Sub


Phil

Did you try the method I described? Did it work? Or are you fixated on
using the technique that you read about "Spreadsheet Control"?

My name is not "Willey" -- Wiley is the publishing company that published a
book of which I was co-author.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access