Calculation based on combo box value

[anb001]

I'm trying to getting some calculations done, based on values in some combo boxes.

I have three silos/tanks, where the contents can be either something called "Barite" or "Bentonite. The content of each specific silo is chosen in a combo box, and the weight of each, is entered into a corresponding text box.

Finally there are two "total" text boxes. One is for total amount of "Barite" and the other one is for the to´tal amount of "Bentonite".

I'd appreciate some ideas how to put this together. I thought of something like this:

IF me.contentA = "Barite" AND me.contentB = "Barite" AND me.contentC = "Barite" THEN ...... ELSEIF ........

But as there are quite a lot of various possibilities, then I assume that this might not be the best solution. In stead I hope that some of you pro's can give me some pointers.

I have attached a screenshot of the setup, which hopefully will give an understandig of what I'm looking fore.

/Anders

 

[RuralGuy]

If you want to keep the same format then put two invisible controls to the right of each row and set the ControlSources to:
=Iff(Me.ContentA = "Barite", txtBoxA, 0)
...etc.
Then sum the two columns for your bottom totals.

 

[anb001]

RuralGuy,

Thanks for your suggestion. I have a problem, though. When entering the controlsource, and run the form, the textbox just shows "#Name?" I assume something is not right in the controlsource, but I can't figure out what. This is what I have:

=Iff([cboMudAContent]="Barite",[txtMudAWeight],0)

The "[]" brackets are inserted automatically.

/Anders

 

[anb001]

Got it now. Error found.

It should be "IIf" and not "Iff"

/Anders

 

[RuralGuy]

Cool! Glad you got it working.

 

[anb001]

I thought I could just use following formula for adding up columns (the invisible text boxes suggested):

=[txtBariteWeightA]+[txtBariteWeightB]+[txtBariteWeightC]

And as such it works ok, if at least one of the boxes contains a "0", if all silos contain the same. If the content of silo A and B is 'Barite' and weight is 1 and 2 tons, and silo C contains 3 tons 'Bentonite'. Result is 3 tons Barite and 3 tons Bentonite. However, if all three silos contains the same, e.g. Barite, (A=1, B=2 & C=3), then result given is '123'.

What am I doing wrong????

/Anders

 

[DCrake]

This is maybe because you have not defined the field type and by default Access is concatenating them together instead of adding them. Set the fields as fixed.

 

[anb001]

Of course

Done, and it works now.

Thanks.

/Anders

 

[RuralGuy]

Nz() might be able to help here.
=IIf([cboMudAContent]="Barite",Val(Nz([txtMudAWeight],0)0),0)