how to sum up the 2 records field

[cheeryki]

If i have 2 records match the statment i set in the code, how to write code to make 2 records (have a total field in the record)sum up together?

 

[beetee]

This question is a little hard to understand.

Try to describe the problem you are having in more detail, and what your goal is (i.e. what information do you or your users wish to see?).

As a wild guess, it would seem the type of problem you are describing would be best be solved by an aggregate query.

Since you are posting in the modules forum, perhaps what you want to know is how to create/open a recordset of an aggregate query in visual basic.

If so, let us know and we'll go from there.

If you want people to put effort into solving your problem, it helps if you put effort into describing it.

 

[StarPassing]

Try using something like:

Dim rs as Recordset
Dim Sql as string

SQL=”Select SUM [FieldName] AS myTotal Where [aField] = Something “

Set rs = db.openrecordset(SQL)

‘ rs!myTotals will now have the sum of ‘FieldName’ for all matching records

 

[cheeryki]

sorry to make you confused, the following is the tasks i want to do

i have 3 Tables (table1, table2, table3) and 1 form (form1). I would like to add a button in form1 and set a event procedure on every click.

1st-> i need to go through all record in table1 and find a record that match current.open form

2nd-> use the record found in 1st and seach the related record from table2 and perhaps will find more than 1 record from table2 that is matched the statment.

3rd-> use the above found record(s) calcuate the total amount and show a msgbox.
the calcuation is = table2!field1 * table3!field2


so i would like to know how to add up if i find more than 1 record that is match the specified statement.


does any one can help because i have try many time but not still fail. many thanks

 

[TonyJollans]

Hi cheeryki,

Exactly what you need to get depends on what you already have. It seems likely that your form is based on Table1 and, so, you already have that record and do not need to get it again. You don’t say what the criteria for selection from Table3 are, but you suggest there will be a single record, presumably matching some field on Form1.

The simplest way to do it that I can see is to use domain functions which you could put in code behind a button or you could put in an unbound text box on your form:

=Dsum(“[field1]”,”[table2]”,”[table2 keyfield] = ” & [table1 matchfield])*Dlookup(“[field2]”,”[table3]”,”[table3 keyfield] = ” & [matchfield])

Replace the bits in red with your values and you should be there. If your form is not based on table1 it will get a bit more complex; post back if that is the case.

Enjoy,
Tony