Calculation Not Working

[June7]

You changed names from [Text107] and [Text108], now:

=[TransactionsBalance]/[SharesOwned]

 

[Gasman]

I wish to thank you all for the suggestions received, BUT we still have not received the answer our Accountant wants.
===
The "TransactionsBalance" is $5,383.81 divided by "SharesOwned" #250 = "AverageShareCost" $21.54

===

Nicole

So you use

=[TransactionPrice]/[SharesOwned]
=([TransactionPrice])/([SharesOwned])
Nicole

:banghead:

You will probably need to use the Round function to get that value as well?

 

[access2010]

June7
As to you suggestion we have tried the following code without any success.

=[Text107]/[Text108]
=([Text107])/([Text108])

I can not find any field with the Name "Text107" or "Text108"
Paul

 

[MajP]

=Sum(Nz([TransactionQuantity])*Nz([TransactionPrice])+Nz([TransactionComm]))/Sum([TransactionQuantity])

I get 21.54

 

[access2010]

MajP

I/we thank you for your formula which W.O.R.K.S

Paul

 

[MajP]

Just remember you cannot do summary calculations on calculated fields
You can not do this

=[TransactionsBalance]/[SharesOwned]

Because [TransactionBalance] is a calculated field =

Sum(Nz([TransactionQuantity])*Nz([TransactionPrice])+Nz([TransactionComm]))

And
[SharesOwned] is a calcuated control =

Sum([TransactionQuantity])

So you just replace the reference to the calculated control with the original calculation for each.

 

[June7]

Paul, which did you go with - referencing the textboxes or the longer formula? As I said in post 21, textboxes were renamed in your last posted db. I showed revised expression which works for me.

@MajP, =[TransactionsBalance]/[SharesOwned] does work in the posted db because those controls are not in an aggregate function. The expression is simple arithmetic using the results of aggregate calcs and that is definitely possible.

 

[access2010]

June7

The code which we find that is working is shown below.

=Sum(Nz([TransactionQuantity])*Nz([TransactionPrice])+Nz([TransactionComm]))/Sum([TransactionQuantity])

Thank you again for your assistance.
Nicole