Insert Sum of Two fields In The Same Record Into Third Field

[k.vallely]

I have a table of customer orders with fields something like this:

customer : order-number : item1 : item1cost : item2 : item2cost : total-cost

I want the total-cost field to be the sum of item1cost and item2cost.
Do I use a query or can I do it in a formula?

Can I do it in my order entry form?
Can I create a dynamic total-cost field that is the sum of the item1cost and item2cost fields and updates when those fields are changed?

Please try not to smirk.
I'm a beginner.
I downloaded this week.

[QuazzieEvil]

you can either do it in a view/query or use OO Basic.

Using a view or query is the simplest method. Simply create a field that is the calculation (rather than selecting a field). The downside is that you may have difficulties creating a form base on a query and be able to update the underlying table.

With basic you can do something like this:

Code: Select all   Expand viewCollapse view

Sub calcField(Event As Object)
REM Bound to 'Text Modified' event of Base & Height controls.
On Error Goto HandleError
   Dim v1 As Double
   Dim v2 As Double
   Dim Form As Object
   
   Form=Event.Source.Model.Parent
   REM calc value of controls Base & Height, and store in Area--bound to table fields with same name
   v1=Form.getByName("Base").BoundField.getDouble()
   v2=Form.getByname("Height").BoundField.getDouble()
   Form.getByName("Area").BoundField.updateDouble(v1*v2)
HandleError:
   If err<>0 Then Exit Sub
End Sub


[normansimonr]

Hi everyone. I have this same question: I used this SQL query:

SELECT <field1>, <field2>, <field3>, (<field1> + <field2>) AS "SUM" FROM <Table_name>

And it worked.

But SELECT <field1>, <field2>, <field3>, (<field1> + <field2>+ <field3>) AS "SUM" FROM <Table_name>

Didn't work. What's wrong? I am using REAL variable types and I have tried a lot of different parenthesis combination. Thinking about k.vallely's question and the mine, can you help us?

Thankyou very much.

[FJCC]

This works for me

Code: Select all   Expand viewCollapse view

SELECT "Track0", "Track1", "Track2" ,"Track0" + "Track1" + "Track2" AS "SUM" FROM .....

It also works with parenthesis around the whole sum. Can you post the exact query you are using. And are you using the embedded HSQLDB or some other DB?

[normansimonr]

Ok. It resulted just taking the parenthesis away; I won't get confused including () in the future. Thanks.

[rudolfo]

normansimonr, please don't run away with this! FJCC stated that the query worked either way.
And that's what I can confirm ... for the embedded database:

Code: Select all   Expand viewCollapse view

SELECT "Track0", "Track1", "Track2" , ( "Track0" + "Track1" + "Track2" ) AS "SUM" FROM .....

works for me, as well.
My experience with SQL statements is that an additonal pair of paranthesis never harms ... except for readability. Even the following really sick way to write the query statement gives the same results:

Code: Select all   Expand viewCollapse view

SELECT ("Track0"), ("Track1"), "Track2" , ( "Track0" + "Track1" ) + "Track2" AS "SUM" FROM .....

So please help others, and don't leave them with the missconception that paranthesis around a field with operators need to be removed. Investigate your situation again and give us some good conclusion.

[kraftwerk]

Hi,

First of all, sorry to be 7 years late into this thread, but I have been searching last three days for a solution to my problem .

I have tried the calcfield procedure shown in this thread. It's great and exactly what I am looking for. Thanks for that.

Before actually testing it on my real database elements, I decided to do a test on a test form and test table inside my database.

To do so, I created a very simple table called TblTest which contain 4 fields: Index (integer auto), FieldA (double), FieldB (double) et FieldC (double)

I created a form that contain these 4 fields with the "wizard".

I created a Module1 in which I pasted the calcfield sub. That Module is linked to the database.

In Calcfield, of course, I changed "Height" and "Base" for "FieldA" and "FieldB". I also changed "Area" for "FieldC".

I affected the calcfield macro to fields FieldA and FieldB on event "Text modification".

I created an entry in the form, I saved it, then I attempt to change either FieldA or FieldB.

With the "basic" debug mode, I can see that the program accesses the macro, but it exits the sub on Handlerror. It does not reach "v1=..." or "v2=...".

It seems that the bug occurs at this line: "Form=..."

Is there something I am doing wrong?

Thanks a lot and sorry again for reigniting a cold thread, but I really need your help guys.

[F3K Total]

Hi,
please supply your file, then you will get a solution.
Otherwise it's like poking around in the dark.
R

[MTP]

The macro provided by QuazzieEvil takes an Event as an input parameter. If you assign the macro to a button on a form and push the button to activate the macro, the "button-pushed" event will be passed to the macro. The Form= line will start with the "button-pushed" event to find the button and then the button's parent, the form object.

(Side note: a form object as in a collection of form controls and potentially other forms, called subforms if they are located inside another form - the "form document" is at a higher level where FormDocument has a DrawPage which has a FormsContainer which has a form or forms that are collections of controls).

If you are just trying to run the macro from the editor, of course there will be no event passed and that line will fail.

Also, the macro manipulates form controls and does not access the underlying table. In addition to having a button on your form document, you would need to have textbox controls named Base, Height, and Area for the macro to be able to complete.

[kraftwerk]

Thanks F3K Total and MTP,

In my case, I am trying to pass "Text modification on the form's txtbox object" as the event. The event, as such, seems to trigger the macro ok, because the program enters briefly the sub.

I have included a database containing only my test table, test form and macro below.

Are there other files required besides the .odb? If so, I'll add them.

Thanks for your time, I appreciate it.

[F3K Total]

Hi,
there where two problems:

• The naming of the controls in the code was wrong, e.g. there is no field called FieldA but fmtFieldA exists, use the Form Navigator to explore.
• If you modify a formatted field, then you can see the changed content, but it wasn't already send. So e.g.

  Code: Select all   Expand viewCollapse view

  Form.getByName("fmtFieldA").BoundField.getDouble()

  cannot read the modified value. By inserting two rows, it does the "Enter" for you.

Here's a code, working for me

Code: Select all   Expand viewCollapse view

Sub calcField(Event As Object)
REM Bound to 'Text Modified' event of Base & Height controls.
On Error Goto HandleError
   Dim v1 As Double
   Dim v2 As Double
   Dim Form As Object
   FormattedField = Event.Source.Model
   FormattedField.commit 'This does the "Enter" for you.
   Form = FormattedField.Parent
   REM calc value of controls fmtFieldA and fmtFieldB, and store total in fmtFieldC--bound to table fields with same name
   v1=Form.getByName("fmtFieldA").BoundField.getDouble()
   v2=Form.getByname("fmtFieldB").BoundField.getDouble()
   Form.getByName("fmtFieldC").BoundField.updateDouble(v1+v2)
   HandleError:
   If err<>0 Then Exit Sub
End Sub

I prefer a version without code, see 2nd form in your modified file attached.
R

[kraftwerk]

Both methods work great on this end too.

I am not sure to understand the difference between the formattedfield and the boundfield.

I have an extensive VBA background, but that version of Basic will require some getting used to for me. I guess I'll have to go through the Basic bible a bit.

You have been of tremendous help . Thanks a million !

I hope this will help other users having the same problem also.

[Villeroy]

The VB(A) code to do the same thing with OpenOffice would be exactly the same. The difference is not the language. The only difference is the thing you talk to in either language.
Yes, it is possible to control OpenOffice through VB(A).

[kraftwerk]

Yes, the way to refer to object and properties is a major difference. It's good to know that's it's not that different for other aspects. I suppose it's like knowing how to ride a bicycle, it comes back to you even years later.

I would gladly change the topic title to 'solved' but I don't have the power to do so since I did not create the thread.

Thanks again you all.

[kraftwerk]

I have a final subquestion on this issue.

I have used method 2 with much success in my database. I noticed the FieldC result is not actually recorded in the table. Is it possible to record FieldC together with FieldA and FieldB when the button is pressed. I know that, for most cases, you would not need to save the result because you have all you need to calculate it in the table, but I will use this result often elsewhere and it will be very useful if it's recorded in the table.

Is there something I can add to the SQL code to record the data of FieldC in the table when the button is pressed?

Thanks.

[kraftwerk]

I'm doing a follow-up on the question presented in my last post, is it at all possible to do record the calculated FieldC value in the table TblTest when button is pressed?

Thanks.

[Villeroy]

Of course it is possible (if you find out how).

[kraftwerk]

Can you help?

[Villeroy]

Can you help?

No, because for various reasons this is bullshit.

[kraftwerk]

I am confident that your attitude does not represent most users of this forum.

[MTP]

Well, first, in a database, calculated fields should not be saved in tables. The exception might be if your database was gigabytes in size and saving a calculated field made some very frequently run query take 5 minutes instead of 20 minutes.

I'm fairly confident your database is not gigabytes in size and your queries all run in fractions of a second. So you should not be saving calculated data in tables.

Second, you haven't commented on what section of the macro code or what part of the form you think needs to be changed. This silence is easily interpreted as "I haven't even tried to understand the help that was provided earlier, I just want other people to do all the work for me". Sharing the efforts you have made so far is a big motivator to volunteers that we're helping you not just with this problem, but to grow your understanding of the software so you can solve future problems on your own.

[kraftwerk]

Hi MTP.

Thanks for your courteous explanation and feedback, it's very much appreciated. I am sorry if my behaviour was perceived that way because I am thankful for all the help.

(In the following text, when I say method #1 and method #2, I am refering to both methods presented by F3K Total in Tue Jan 05, 2016 1:15 pm post.)

I tried both method #1 (macro) and method #2 (no macro) and I had decided at the time to go forward with #2 as it was recommended as better by F3K Total. Method #2 is indeed very elegant.

However, I noticed the following, only yesterday, after performing further tests with Method #1.

Method #1 does write the calculated value in the table. And method #2 does not write the calculated value in the table. So #1 gives me something closer to what I was looking for. I used that code and I ended up associating it with a button click instead of a txtchange event. So the visible effect for the user resembles very much method #2.

It was hard for me to add more details than I provided in Wed Jan 06, 2016 1:58 pm post. My question was, for method #2, is there any additional SQL code I can add to save the value in the table? (and that was before I noticed that method #1 did that already). I googled around first to see if SQL can do this in Open-Base, without success. I had not much progress to report on that.

I am sorry if this was interpreted as insufficient information or borderline silence, it was not my intention.

Take care and thanks again.