please help

  • Thread starter Thread starter jerry
  • Start date Start date
in a new unbound field on your form, put the following...


= [SomeFieldName] * [SomeOtherFieldName] * [SomeOtherOtherFieldName]


Hope that helps,
Rick B
 
Thanks Rick,but this is not what I meant,Maybe I was not clear enough.
It is just one field and more rows,
I need some thing like =sum(fieldnm),but no sum,I need
product
Hope that I`m clear now

Thnks

Rick B said:
in a new unbound field on your form, put the following...


= [SomeFieldName] * [SomeOtherFieldName] * [SomeOtherOtherFieldName]


Hope that helps,
Rick B


jerry said:
how can I find the product of numbers.
(X*b*n*ect)
I have no idea,can anyone help me?
Click to expand...
Click to expand...
 
Thanks Rick,but this is not what I meant,Maybe I was not clear enough.
It is just one field and more rows,
I need some thing like =sum(fieldnm),but no sum,I need
product
Hope that I`m clear now
Click to expand...

There's no builtin product function analogous to Sum. You could write
one in VBA, but a sneaky getaround would be to sum the logarithms:

=Exp(Sum(Log([fieldnm]))

Of course this value could get VERY big very quickly if you have many
rows!


OffTopic: there's a good "sucker bar bet". I pick the *worst* baseball
team in the country; you pick the best (I'll even let you pick both
teams). At the end of the season, you pay me the sum of my team's
scores in all games; I'll pay you the PRODUCT of your team's scores.

No... I won't make this bet for basketball, cricket, or (American)
football, but I'd be willing to do so in soccer.

John W. Vinson[MVP]
 
Jerry,

Here's one solution. Place the following code in your form's module and
change FieldName to the name of your field. Now, instead of =
sum([FieldName]) you use = flProduct("FieldName"). It will return 0 if
there are no recordsets or if there is an overflow.


Private Function flProduct(fField As field) As Long

Dim rst As DAO.Recordset
Dim lProduct As Long

on error goto Function_Error

Set rst = Me.RecordsetClone

With rst
If Not (.BOF Or .EOF) Then
lProduct = 1
.MoveFirst
Do Until .EOF
lProduct = lProduct * CLng(.Fields(sField).Value)
.MoveNext
Loop
End If
End With

Function_Exit:
Set rst = Nothing
flProduct = lProduct
Exit Function

_Error:
flProduct = 0
Goto Function_Exit

End Function
 
Jerry,

Place the code in the form's code module. Assuming the field name is "odd",
add a text box to the form's footer section and put =flProduct("odd") as the
controlsource. Note the quotes around "odd". John Vinson's solution is
more elegant than mine, you my try it also.

Thanks.
 
I notice from your other thread that your fields are floating point. Here
is a revised function:

Call as =fdProduct("amount")



Private Function fdProduct(dField As field) As Double

Dim rst As DAO.Recordset
Dim dProduct As Double

on error goto Function_Error

Set rst = Me.RecordsetClone

With rst
If Not (.BOF Or .EOF) Then
dProduct = 1
.MoveFirst
Do Until .EOF
dProduct = dProduct * CDbl(.Fields(sField).Value)
.MoveNext
Loop
End If
End With

Function_Exit:
Set rst = Nothing
fdProduct = dProduct
Exit Function

_Error:
fdProduct = 0
Goto Function_Exit

End Function
 
Back
Top