How to do math with two different units of measure

[bigalpha]

I have a table with some prices that are charged by the pound and some that are charged by each.

tblPrices
ID 1 - 1.00 per pound
ID 2 - 1.50 per pound
ID 3 - 2.00 per pound
ID 4 - 5.00 each
ID 5 - 7.00 each

My form is set up so that way I count how many containers I have and their weight, separately. I need ID 1 through 3 to do the math based on the pounds (essentially $1 per pound at 36 pounds) and ID 4 and 5 to do math based on how many of each are there ($5 each, and there are 3 of them).

I'm not exactly sure the best way to set this up so it's as automated as possible.

 

[bob fitz]

What is the name of the control on the form that shows "many containers I have" and what is the name of the control that shows "their weight".

 

[bigalpha]

What is the name of the control on the form that shows "many containers I have" and what is the name of the control that shows "their weight".

Main Form
"how many containers I have": 1348Comments
"Their weight": pounds

Subform: subfrmInputEtid
The combo box with the price list: cboClinList

 

[bob fitz]

With the introduction of the sub form and a combo box I think it would be simplest if you can post a copy of the db in A2003 mdb format for us to examine.

 

[bigalpha]

With the introduction of the sub form and a combo box I think it would be simplest if you can post a copy of the db in A2003 mdb format for us to examine.

Okay. I'll post it in A2003 format.

 

[bigalpha]

Okay, here's my full db. I scrubbed out a lot of data but left enough so that you should be able to see what I'm doing.

I'm still pretty new to Access so I'm sure there's a lot of things that could be done better. I'm continually working on this to improve it.

Thanks for your help!

 

[bob fitz]

I can't see your db attachment

 

[bigalpha]

I can't see your db attachment

Re-uploaded.

 

[bob fitz]

OK I have downloaded the db. Can you tell me which form I need to examine.

 

[bob fitz]

OK I'm guessing the form to look at is frmInputWaste. If that's right, can you post another db that has data in 1348Comments with an explanation of the calculation to be done in txtdisposalcost

 

[bigalpha]

OK I'm guessing the form to look at is frmInputWaste. If that's right, can you post another db that has data in 1348Comments with an explanation of the calculation to be done in txtdisposalcost

You are correct.

The calculation that should be done is the following:
1. If the CLIN is priced 'per pound', it should multiply Cost Per Pound and Pounds.

2. If the CLIN is priced 'each', it should multiply Cost Per Pound and a text field with the number of items [we can use 1348Commnts for this]. This text field will be manually entered.

I modified the two test records to more easily see this. Record 'Test 2' is correct: it's multiplying Cost Per Pound by Pounds. Record 'Test 1' is incorrect: it should multiply Cost Per Pound by 1348Comments.

tblClin has a list of all the CLIN numbers and how they are priced.

 

[bob fitz]

Take a look at the attached db .
I have added a hidden column to the combo box called “cboClinList” so that it now holds the data from “ClinUnit”.
I have set the “Control Source” property of “txtdisposalcost” to:
=IIf([cboClinList].[column](4)="lb",[cboClinList].[column](3)*[Forms]![frminputwaste].[pounds],[cboClinList].[column](3)*[Forms]![frminputwaste].[1348Comments])

This expression references the hidden column and uses the Iif() function to determine the calculation that should be used.
I also removed your expression from the “Default Value” property.

The label which read “Cost per Pound” has been changed to a text box with the following expression as it’s Record Source property:
=IIf([cboClinList].[column](4)="lb","Cost per Pound","Cost Each")

Which changes the text displayed, depending on the value in the hidden column of the text box.
Hopefully, this is what you wanted, but if not, please post back.

 

[bigalpha]

Take a look at the attached db .
I have added a hidden column to the combo box called “cboClinList” so that it now holds the data from “ClinUnit”.
I have set the “Control Source” property of “txtdisposalcost” to:
=IIf([cboClinList].[column](4)="lb",[cboClinList].[column](3)*[Forms]![frminputwaste].[pounds],[cboClinList].[column](3)*[Forms]![frminputwaste].[1348Comments])

This expression references the hidden column and uses the Iif() function to determine the calculation that should be used.
I also removed your expression from the “Default Value” property.

The label which read “Cost per Pound” has been changed to a text box with the following expression as it’s Record Source property:
=IIf([cboClinList].[column](4)="lb","Cost per Pound","Cost Each")

Which changes the text displayed, depending on the value in the hidden column of the text box.
Hopefully, this is what you wanted, but if not, please post back.

That works perfectly; it's exactly what I need. I didn't realize that the solution would be as simple as this. I didn't realize that you could calculate math in an IIF string like that.

 

[bob fitz]

That works perfectly; it's exactly what I need. I didn't realize that the solution would be as simple as this. I didn't realize that you could calculate math in an IIF string like that.

Good. I'm glad that works for you. Good luck with your project.

 

[bigalpha]

Good. I'm glad that works for you. Good luck with your project.

Thanks, I need all the luck I can get

 

[bob fitz]

Thanks, I need all the luck I can get

When the luck runs out, you can always post here.

 

[bigalpha]

When the luck runs out, you can always post here.

I usually spend a day researching and trying things, then if I can't figure it out, I post here. This place is amazingly invaluable!!