SELECT a product of twho fields

[jaanisf]

multiplication in SELECT statements

I need an sql statement in my form which selects a product of two fields in a table and then counts them all together.
The statement should look as follows:

sum1 = "SELECT Count([Field1] * [Field2]) WHERE [Field3] <> 'ml' AND [FieldDate] >= txtDate1 AND [FieldDate] <= txtDate2

sum2 = "SELECT Count([Field1] * ([Field2]/100)) WHERE [Field3] = 'ml' AND [FieldDate] >= txtDate1 AND [FieldDate] <= txtDate2

txtSum = sum1 + sum2

When I input dates in Text Boxes txtDate1 and txtDate2, and press button btnCalculate, I need the sum of sum1 and sum2 to be filled automatically in a Text Box txtSum.

How should the code look like, to be written behind a button's btnCalculate on click event?

Thanks in advance.

 

[suepowell]

I am a bit confused by your question.
Do you want to sum the values given by field1 * field2, or just count the instances.

Assuming you want the sum, you would need to do something like this:

open a recordset (this code depends on the version of access you are using) with the following sql string.
"SELECT Field1,Field2 from tablename WHERE Field3 <> 'ml' AND FieldDate >= " & me.txtDate1 & " AND FieldDate <= " & me.txtDate

runsum = 0
recordsetname.movefirst
do until recordsetname.eof

sum1 = recordsetname!field1 * recordsetname!field2
sum2 = sum1/100

runsum = runsum + sum1 + sum2

loop

txtsum = runsum

obviously you need to declare the variables etc.

I hope this helps and I have understood your question correctly.

Sue

 

[jaanisf]

Thanks for your help
But I already found the solution:

txtSum = DSum("([Field1]*([Field2]/100))", "Table1", "[Field3]='ml' And [FieldDate] >= txtDate1 And [FieldDate] <= txtDate2") + DSum("([Field1]*[Field2])", "Table1", "[Field3]<>'ml' And [FieldDate] >= txtDate1 And [FieldDate] <= txtDate2")

I put it behind txtDate2 Focus Lost. In case anyone else needs.