calculated control sources

[astath]

Hello,


I'm encountering an issue that I would like your help with. I'm using some textbox fields in a form in order to perform some calculations. fields are Qty / unit price / discount / total. for discount i'm using a dlookup as a control source and for total i'm using =[Price]*((100-[Discount])/100)*[QTY]. They both work without issues. When though I try to create a subtotal that would need the sum function along with the above, Access can't calculate. I'm guessing that this has to do with dlookup.

Any thoughts?

Thanks!

[Bob Fitz]

Try putting the calculation in the "Sum" text box: =Sum([Price]*((100-[Discount])/100)*[QTY]).

Or you could try putting the calculation in the form's Record Source query

[astath]

Thank you for your recommendation but I have already tried so and the result is the same. Could this be due to me using dlookup for the discount field?

[Bob Fitz]

For what part of the expression is Dlookup being used to return a value

[astath]

The value of the Discount field comes from a dlookup. To make the situation more challenging for Access Dlookup is being run on a query. Strange part is that the whole scenario works without issues when used to fill in fields of a form, meaning it completes the fields needed without issues (both the Dlookup and the calculations). The only thing that is producing an error is the use of sum function for a different textbox.

[Bob Fitz]

Where are you using the Sum() and when you say Access can't calculate, what exactly does that mean. Error message? Just no result?

[Bob Fitz]

Put all the calculations in the query used as the form's Record Source property and you should have no problem with using Sum() on the form

[astath]

Actually I am to blame as I failed to give you a clear picture of the scenario. The form in question had a subform from which i was trying to get the sum. That failed (giving the valus #error to the textbox of the parent form) since i needed to calculate the sum within the subform and then transfer the value to the parent form.

Now it worked but to ensure extra fun the sum calculation is wrong. I tried to figure out a patern in order to understand the muliscalculation without success Most probably it's another stupid mistake of mine but I'll check it tomorrow being hopefully well rested. Thank you for you help!

[astath]

A related issue to the above. As mentioned discount comes from a query. Scenario is that the actual discount value is stored in a table named Charges with fields company, site and discount. The query is used with a subform of a form that has comboboxes with the values needed for the query. Subform had a textbox discount and was being filled in without issues. What I noticed is that the calculations were wrong cause the discount value was not stored in the table that the subform table was based on. I have changed the discount field of the subform from textbox to combobox, setting both the control source to point to the appropriate field and the row source to the query. Also when I run the query with the form open the result is ok. But when I'm trying to fill in the data the discount combobox is empty.

Any ideas?