Calculated field in form not working (1 Viewer)

[PicassoB]

Can anyone spot what is wrong with my formula for this field, copy of the form enclosed, also a part of my DB system that is giving me some problems

would appreciate help as I have spent hours looking for a solution and trying many different ways to get this to calculate

 

[ajetrumpet]

you have an aggregate function with just an complex expression inside it. Drop the SUM() function and just leave the rest and you should be fine. Remember to include the order of operations too! You're missing a few parenthesis!

 

[PicassoB]

Hi Adam
Thanks for looking at this ,I had already tried with the SUM() droped but this only gives me the first cost in the list, I need a total of the continuous form

 

[ajetrumpet]

I didn't get a chance to look at the database, because I am at work right now, but if you are wanting a total of the continuous form, you're going to want a function something like a domain aggregate function, like DSUM(), so you can sum all of your numbers from the underlying table.

If you need help with this, and noone has gotten to it, I might try it with your database when I get home. Just to help you get on your way...

 

[PicassoB]

Thank Adam
would appreciate that

I am a bit of a novice when it comes to coding allthough I have picked a lot of tips up from the forum. always some good guy willing to help

 

[missinglinq]

This does what you want for Cost and Text27 (you really need to replace the Access given names with meaningful ones.)

Control Source for Text27
=Sum([Quantity]*([UnitPrice]+([UnitPrice]*([MarkUp]/100))))

Control Source for Cost textbox
=[Quantity]*([UnitPrice]+[UnitPrice]*([MarkUp]/100))

 

[PicassoB]

tried the formula given but stil came up with a error - did you get the copy DB to work?

 

[missinglinq]

Yes I did! Both Cost and Text27 worked as they should. Now you do have to be careful, when copying and pasting the formulas I gave you into the Control Source Properties. You have to paste them in, then click on any other property, then exit the property box. If you simply pasting it in then exit the property box, the Control Source reverts back to the old expression. This is a known bug with 2003. These two formulas do work exactly as you intended.

The file's attached.

 

[PicassoB]

Great thanks very much - I had not changed the text27 to MarkUp I had been struggling with all the parenthesis for hours I had missed the obvious
again much appreciate the help

 

[ajetrumpet]

Congrats to Linq!

 

[missinglinq]

These things tend to drive me nuts, also! I usually end up putting it on paper, using different colors for the various matching parentheses, until I get the order of operation straight. As Adam said earlier, that's the important thing in this kind of expression.

Have a great weekend!

Linq