Adding value based on a cmb Box 1

[zyphac]

I have a combo box with 3 values(rock, sand, Dirt). I have a tons box in which to input tonage. At the bottom of the form I have the totals boxes(sand, rock, dirt). I forgot to mention the combo box is in a sub form in datasheet view. I can get it to add every thing in the tons text box, but I can't figure out how to get the totals for each textbox totals(sand, dirt, rock).

here is what I got now:
tons total: =Forms!frmlogSheet!subfrmLogSheet.Form![Ton Total]
Then in the sub form a hidden txtbox: =Sum([Tons])

Thanks
zypha
Happy Easter

 

[Steve101]

Make sure that those hidden text boxes with the =Sum() formulae are in the Footer section of your subform - make them available via the View, Header/Footer menu options from the menu bar.

Whilst these will not display anyway in datasheet view (even if they're not hidden), they will allow the summation of the corresponding detail entries to compute correctly, and they can be referenced from the main form, to return their values to it.

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[zyphac]

I have them in the footer section. What I am trying to do is: Say you select rocks out of the cmbbox. the go to the tons text box enter 23, next line select sand, enter 100. so on and so on. On the total boxes of the main for add the total of sand, rock, etc. The way i have it now it just adds the tons. I have a total box for sand, rock, dirt, etc. how can I get them to add them?

Thanks
zypha

 

[Steve101]

Still not sure I fully understand. Are you accumulating three different subtotals on the subform, or just one.

If you want to accumulate separate subtotals on the subform, but the data is only going into a single field, then perhaps, you can use the following syntax for the three Sum controls in the Footer section of the subform.

SumSand: Sum(iif(cmbYourCombo='Sand',Tons,0)
SumDirt: Sum(iif(cmbYourCombo='Dirt',Tons,0)
SumRock: Sum(iif(cmbYourCombo='Rock',Tons,0)

These controls should then only sum up the corresponding amounts as per the selected combo type.

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Steve101]

Correction to the above code syntax. It should read:

SumSand: Sum(iif(cmbYourCombo='Sand',Tons,0))
SumDirt: Sum(iif(cmbYourCombo='Dirt',Tons,0))
SumRock: Sum(iif(cmbYourCombo='Rock',Tons,0))

ie. close brackets correctly.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[zyphac]

I made a sand, dirt, rocks text box in the footer of the subform. Then in the control source:
Sum(iif(cmbproducts='Sand',Tons,0))

it gives me the error #Name?

This is for the sand txtbox, I have not put the syntax in the other two boxes.

Thanks zypha

 

[Steve101]

Put an equal symbol in front of the Sum function; ie:

=Sum(iif(cmbproducts='Sand',Tons,0))

Hopefully this will sort it out,




Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[zyphac]

It still gives me an error, it also changes the syntax:

from this:
=Sum(iif(cmbproducts='Sand',Tons,0))

to this:
=Sum(IIf([cmbproducts]='Sand',[Tons],0))

I am using access 2002, ,could this be why?


Thanks
zypha

 

[Steve101]

Most curious; I have a simple example working perfectly (A2K) ... should'nt think 2002 has anything to do with it.
Send me your email, and I'll post a working example to you,

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[zyphac]

Thanks Steve!!!!!!

Mr Lewy has come up with the anwser:
=Sum(IIf([Products]='1',[Tons],0))

So if you want to know how to get totals base on a cmbbox value here you go. products = cmbbox name, tons = input value textbox name.


Cheers