Credit Amount Not Working

[access2010]

We are using Ms Access 2003, but the Credit Calculation is not working for (A) Sold Transaction Totals and (B) to show the Credit Balance in Red.

Your Suggestion will be appreciated
===
Can I please receive a suggestion on how to create a calculation to find out the average cost of the Equities that we still own?

===
Thank you

 

[June7]

Two negatives multiplied is a positive result. Why is the price of Sold product a negative? How can anything have a negative price? Always enter price as positive value.

Advise not to use punctuation/special characters (underscore only exception) in naming convention.

 

[access2010]

Thank you for your reply.

(A) Two negatives multiplied is a positive result.
Thank you for pointing out our coding error, but how would you suggest entering a negative value (CR)?

(B) Why is the price of a Sold product a negative?
This form controls the Purchase (DR) and Sale (CR) of equities in our Charitable Endowment.

(C) How can anything have a negative price?
When an Equity is sold the Quantity and Value is a decrease (CR) in the Endowments holdings.

(D) Always enter price as positive value.
The Government Auditors have requested that Purchases of Equities are to be entered as (DR) and Sales are to be entered as (CR)

Advise not to use punctuation/special characters (underscore only exception) in naming convention.
We are using an old Database and for all new items, your suggestion will be followed.

What is your suggestion for what we should do so that if the “TransactionTotal” field has a (CR) negative value the figure would show in red with an open and closed bracket?

I/We do appreciate your suggestions.
Nicole

 

[June7]

Enter quantity as positive or negative. But price is always a positive. Then when you multiply the two values, you get either DR or CR transaction total amount. The Conditional Formatting will then work properly for the total.

However, you are displaying rounded values yet calculation uses the non-rounded data:

Because of Format setting, textboxes show:
(-650 * 6.31) + (-10.09)

Which equals -4111.59

Yet your total returns -4108.34 because it calculates with price 6.305

Also, the Average Share Cost is not an average because the expression uses Sum(). Avg() would average the Price.

 

[access2010]

I can smell success with your assistance.

Could you please suggest a way to;
A = Find the Average cost of the Shares Owned
B = Copy Portfolio Code and create a new blank record which contains the Portfolio Code

Thank you,
Nicole

 

[June7]

So what's wrong with the Average calc result?

Copy which Portfolio Code - the last entered? Do you want that entered value carried forward during a data entry session? Do you want to automatically populate that field when form is first opened?

 

[access2010]

Average Cost.
The way that our Auditor wants to see our Average cost of the DR equities, is as shown on the attached word document.

Copy the previously entered Portfolio Code.
Yes = Do you want to automatically populate that field when form is first opened?

I/We appreciate your assistance in helping us.

Nicole

 

[June7]

If you want to average only the Bought prices, try:

=Avg(IIf([TransactionQuantity]<0,[TransactionPrice],Null))

But don't you have to consider the share price in effect when sold?

Do you need to do something like inventory value calculation - FIFO (First In First Out)? You have only 200 shares remaining - should those shares be considered part of the 1000 shares in the last purchase and valued at 4.22/share?

Shouldn't transactions be listed chronologically regardless of type?

As for picking up the PortfolioCode, consider:

Private Sub Form_Load()
Me.PortfolioCode.DefaultValue = "'" & DLookup("PortfolioCode", "Investments_Purchases_SalesT", "TransactionDate=#" & DMax("TransactionDate", "Investments_Purchases_SalesT") & "#") & "'"
End Sub
 
Private Sub PortfolioCode_AfterUpdate()
Me.PortfolioCode.DefaultValue = "'" & Me.PortfolioCode & "'"
End Sub

 

[arnelgp]

another way of getting the default value for the PortfolioCode using the Current event of the form:

Private Sub Form_Current()
    With Me.RecordsetClone
        If Not (.BOF And .EOF) Then 
            .MoveLast
            Me.PortfolioCode.DefaultValue = "'" & !PortfolioCode & "'"
        End If
    End With
End Sub

 

[access2010]

Thank you for your assistance, Arnelgp.
Your code has been helping us enter data faster.
Could you suggest a code that will also MoveLast, Currency?

Thank you for your assistance.
Nicole

 

[access2010]

June7
Thank you for trying to assist us with finding an average price, but the results are not satisfactory for our auditor.

We will try a few manual calculations in Excel until we can find the proper formula.

Nicole

 

[Gasman]

Your auditor should be giving you the exact formulae they want you to use.

 

[June7]

Right, how is auditor determining the results are not correct? They must be calculating in order to make that assessment. Which means they have an algorithm. They should be able to provide you the steps. If you have that then it can be programmed.

 

[arnelgp]

just add the currency to the code:

Private Sub Form_Current()
        With Me.RecordsetClone
        If Not (.BOF And .EOF) Then
                .MoveLast
                Me.PortfolioCode.DefaultValue = """" & !PortfolioCode & """"
                Me.Currency.DefaultValue = """" & !Currency & """"
        End If
        End With
End Sub

 

[access2010]

Arnelgp,

P.E.R.F.E.C.T

thank you, Paul

 

[access2010]

Our Endowment Auditor would like the form ending with 2019 adjusted if you can help us.

A = the total works if B is not used
B = sum of the column is not working
C = average cost of the shares owned is not working

How can we get a total of these shares held at different institutions
D = if the portfolio code = I-Bns
E = if the portfolio code starts with = TD_
F = if the portfolio code starts with = Rbc_

Your assistance will be appreciated.

Thank you,
Nicole

 

[June7]

Can't figure out why your form won't do the aggregate calcs. I created a new form bound to Investment_Purchases_SalesT. Added 3 textboxes in Footer with expressions:

=Sum([TransactionQuantity])

=Avg([TransactionPrice])

=Sum(IIf([PortfolioCode] Like "*BNS*",[TransactionQuantity],0))

This form works and these are the same calcs I tried on your original form. I am at a loss. Sometimes weird issues are due to corruption that cannot be explained or fixed and require rebuilding at some level. I tried copying all the controls to a new form object. Still didn't work. So next step is to try importing everything to a new database file. If that fails, might have to rebuild form from scratch. So unless someone else can see why your form won't work, you are stuck.

 

[access2010]

June7

T.H.A.N.K. you

Your suggestions work and our forms should now satisfy our Accountant.

We have had the same problem as you have had with the "aggregate calcs" and have given up for now.

Paul