Calculation Not Working (1 Viewer)

access2010 · Jan 7, 2019

Could we receive an idea why the form, "Investments_Purchases_SalesF_SubForm=Error" does not work and causes all other calculations on the form to show an error?

While the form, "Investments_Purchases_SalesF_SubForm=Works" has no errors.

Any suggestions, will be appreciated.

Thank you,
Nicole

June7 · Jan 7, 2019

As noted in your other thread, this could be corruption that will never be explained. Suggest you keep the good one and get rid of the bad and move on. I had to do that a few times during the early stages of developing an app.

access2010 · Jan 7, 2019

June7
Thank you for your note and this is what we have done, BUT, I was hopping that someone might find the way to fix our problem.

Nicole

June7 · Jan 7, 2019

You may have already accomplished the most reasonable 'fix' possible - created a new form.

You can try a decompile procedure to see if that corrects the issue, however, will not explain what caused it, and sometimes even that will not fix. http://www.fmsinc.com/MicrosoftAccess/Performance/Decompile.asp

Dreamweaver · Jan 7, 2019

This happened to me a few times years back when Programs I worked on errored and I started debuging making changes to the code then going back to the form without saving after the save warning popup, would currupt that form I backup all my projects on a daily basis and if I've spend a lot of time on a project I'll back it up. Haven't had it happen since I started using office 365 but still backup.

access2010 · Jan 7, 2019

MickJav
Thank you for your suggestion.
If we plan to make any changes to our database, we copy the original to a folder with to-days date and compile and make a backup to the same folder as the day's changes continue.

Paul

MajP · Jan 7, 2019

You cannot calculate a calculated control. You have to go to the source
Should read
=Sum(Nz([TransactionQuantity])*Nz([TransactionPrice])+Nz([TransactionComm]))

access2010 · Jan 7, 2019

June7
Thank you for your link to use the Decompile function.
The data base with the problem went from 27,696 KB to 23,044 KB

We have tried recreating the form MANY times but an error always occurs when we try to add the contents of the field "TransactionTotal".

Could we save the data to a NEW field (TransactionTotal) in the Table "Investments_Purchases_SalesT" and than be able to Add this NEW Column to find the TransactionTotal (DR-CR).

If we have the TransactionTotal we could than find the Average Cost of each Share?

If yes, how could we place the calculation into the new field (TransactionTotal) in the table?

Thank you,
Paul

MajP · Jan 7, 2019

See post 7
If you replace
=sum([TransactionTotal])
with
=Sum(Nz([TransactionQuantity])*Nz([TransactionPrice])+Nz([TransactionComm]))

it works all values are shown.

MajP · Jan 7, 2019

See detailed explanation. My solution uses method 1.
http://www.jegsworks.com/lessons/databases/formsreports/step-totals-calculated.htm

access2010 · Jan 7, 2019

MajP
I can smell S.U.C.E.S.S

Could you please advise me of how to fix our average calculation and than our form will be complete?

Thank you,
Paul

MajP · Jan 7, 2019

=Sum([TransactionPrice])/Sum([TransactionQuantity])

June7 · Jan 7, 2019

Now I do feel like idiot. I did make sure TransactionQuantity was a normal field in table but apparently overlooked TransactionTotal. However, I do find it bizarre that error in TransactionTotal causes the valid expressions to error as well.

Might want to correct textbox name Average_Tranaaction_Price to Average_Transaction_Price

Should not divide TransactionPrice by TransactionQuantity. TransactionPrice is already cost per unit.

=Sum([TransactionPrice])/Sum([TransactionQuantity]) = 0.43

=Avg([TransactionPrice]) = 15.31

What needs to be considered in calculating average? Should this include the commission?

MajP · Jan 7, 2019

Should not divide TransactionPrice by TransactionQuantity. TransactionPrice is already cost per unit

I feel like an idiot too, did not look at the data just the formula.

However this is probably also wrong unless you really want the mean average price paid.

Code:

=Avg([TransactionPrice]) = 15.31

That is an average of an average.

I believe the correct is

Code:

=Sum([TransactionPrice]*[TransactionQuantity])/([SharesOwned])

which is 18.99 per share.

Example
You buy 100,000 shares with a transaction price of 1 per share.
You buy 1 share with a transaction price of 100 dollars.
The average of the transaction prices paid is 101/2 or 50.5

But not the average cost of all your shares.
100,100/100,001 which is basically a little more tha 1

June7 · Jan 7, 2019

Maybe you mean:

=Sum([TransactionPrice]*[TransactionQuantity])/Sum([TransactionQuantity]) = 18.99

or

=Sum([TransactionPrice]*Abs([TransactionQuantity]))/Sum(Abs([TransactionQuantity])) = 9.61

MajP · Jan 7, 2019

Another good catch. Was not even paying attention to the negatives. The latter looks correct.

June7 · Jan 7, 2019

Right, OP needs to decide if they want the average share price regardless if bought or sold OR the average price of net quantity.

access2010 · Jan 8, 2019

I wish to thank you all for the suggestions received, BUT we still have not received the answer our Accountant wants.
===
We would like to find the Average cost of each share purchased by dividing the "TransactionsBalance" by "SharesOwned" which would than give us the "AverageShareCost"
I think that we have tried all the suggested formulas without any success.

The "TransactionsBalance" is $5,383.81 divided by "SharesOwned" #250 = "AverageShareCost" $21.54

===
Your suggestions, will be appreciated on how to find the Average Share price.
Thank you.
Nicole

June7 · Jan 8, 2019

Well, that is an explicitly stated algorithm and all we needed.

Simply reference those two textboxes:

=[Text107]/[Text108]

access2010 · Jan 8, 2019

June7
Thank you for your suggestion and we have tried the following code without receiving the answer that we require.

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

Nicole

Calculation Not Working (1 Viewer)

Registered User.

Attachments

AWF VIP

Registered User.

AWF VIP

Well-known member

Registered User.

You've got your good things, and you've got mine.

Registered User.

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

Registered User.

Attachments

You've got your good things, and you've got mine.

AWF VIP

You've got your good things, and you've got mine.

AWF VIP

You've got your good things, and you've got mine.

AWF VIP

Registered User.

Attachments

AWF VIP

Registered User.

Similar threads

Users who are viewing this thread