Text box to show the SUM of [Net], which is a calculated field based on [Sold]*[Product_Price].

[Nikki Harry]

Hi all,

Background:



2 linked tables [02Transactions] and [03Products].

1 continuous form [TransactionBase] which takes data from both tables.

3 text boxes (of interest) on the form:
[Sold] - created with the form, the value of which is specified by the form's text box control source [Sold].
[Text25] (To be renamed 'Product_Price') - an added calculated text box, the value of which is derived by a DLookUp statement that gets the relevant value for each record from the table [03Products].
[Text28] (which will be renamed to 'Net') - an added calculated text box that contains =[Text25]*[Sold] in the control source .

Everything to this point works as it should.

The Problem:

I created a text box [Text36] (To be renamed 'Total Net') and placed it in the form footer. Despite my best efforts in following the examples, including the obvious =SUM([Text28]), nothing I have placed in the control source has returned anything else but #Error.

Is it just that Sum doesn't like operating on calculated fields?

The Solution:

This is where you good folks get to throw your ideas at me

Thanks all

NH

[alansidman]

Maybe Dsum instead of Sum

https://www.techonthenet.com/access/...omain/dsum.php

[Nikki Harry]

Thanks for the effort Alan. Unfortunately I still get the #Error msg in the text box. Something Access doesn't like about Sum(ing) a calculated form based text box, especially when one of the fields used to arrive at the calculated text box value gets its value from DLookUp statement to another table. ho hum.

[Bob Fitz]

Something Access doesn't like about Sum(ing) a calculated form based text box, especially when one of the fields used to arrive at the calculated text box value gets its value from DLookUp statement to another table. ho hum.

Perhaps try using the DLookup() expression in the Sum() function rather than referring to a control that's using DLookup().

[Minty]

Move the calculations to the forms underlying query - then things will get much easier, as you can then use the Sum Functions on your form.

You can't normally sum a calculated form field.

[Nikki Harry]

Problem Solved! Thanks Minty, I was a few steps behind you.
Created a query that got rid of all the trying to grab data from different tables. After that a simple =Sum([Sold]*[Price]) worked like a dream.

Alan, Bob, thanks for the input.

Question: Who marks these things SOLVED? I do it, or an adult?

[Minty]

Glad that sorted it. As a general rule of thumb anything like that is not only easier to work with in the query, but faster to run as well.

Solved - You can - or ask a responsible adult
At the top of the forum is a button "Mark Thread as Solved" Press that!

[June7]

Any poster in a thread can flag 'solved' - done.