Calculating Txt Box in SubForm

[Guest]

I have a subform that has the Following Fields:

Date
Equipment = combobox
Qty = txtbox
Recieved = checkbox
Shipped = checkbox
Category = Combobox
Comments = memo

I have a text box that I would like to show me my handling cost per
equipment piece. But it is based on Category value. The value in the
Category field must say Saddles. I can't figure out how to write it.

For Example: Qty = 30 * 1.25 if Category = Saddles.
Handlingtxt = $37.50

Can some one help?

 

[Amy Blankenship]

You'd need a table CategoryHandlng, then just include that into the query
you use for the recordsource for the form.

HTH;

Amy

 

[Guest]

Tony,

The syntax of the IIF function is:

=IIf(<expression>, <value if expression is true>, <value if expression if
false>)

so for your case,

=IIf([Category]="Saddles", [Qty]*1.25, 0)

Note, however, that the *value* of your combo box may be different than what
is displayed. The value corresponds to the Bound column. Often this will be
an integer value.

Sprinks

 

[Guest]

Tony,

Amy makes a good point--if you have more than one multiplier, it would be
better to have it in a table. You don't need another table, however; you
could add a field to the existing Category table, called, e.g., Multiplier.
Then include this as a column in the Category combo box, and refer to it
using the Column property, which starts numbering from zero. So if the
Multiplier is the third column, e.g., the expression becomes:

=Nz([Qty])*Nz([YourComboBox].Column(2))

Nz will avoid an error if Qty or the Multiplier are zero.

Sprinks

 

[Guest]

The more I think about this it is a little more complicated then what I
originally said. I also have to make sure that the following is true/False

For Example: Qty = 30 * 1.25 if Category = Saddles and Received is True.
Handlingtxt = $37.50

I do have a table for the Category already. It lists "Saddle", "Boom",
"Other", "Bags" in it. That is all. So are you saying add another column
for example:

Category Cost
Saddle 1.25
Boom 35.00
Bags 0.00
Etc....

Tony,

Amy makes a good point--if you have more than one multiplier, it would be
better to have it in a table. You don't need another table, however; you
could add a field to the existing Category table, called, e.g., Multiplier.
Then include this as a column in the Category combo box, and refer to it
using the Column property, which starts numbering from zero. So if the
Multiplier is the third column, e.g., the expression becomes:

=Nz([Qty])*Nz([YourComboBox].Column(2))

Nz will avoid an error if Qty or the Multiplier are zero.

Sprinks

I have a subform that has the Following Fields:

Date
Equipment = combobox
Qty = txtbox
Recieved = checkbox
Shipped = checkbox
Category = Combobox
Comments = memo

I have a text box that I would like to show me my handling cost per
equipment piece. But it is based on Category value. The value in the
Category field must say Saddles. I can't figure out how to write it.

For Example: Qty = 30 * 1.25 if Category = Saddles.
Handlingtxt = $37.50

Can some one help?

 

[Guest]

Yes, I'm suggesting to add another column. This would multiply the qty if
Received is True. With this condition, the expression is:

=IIf([Received]=True,Nz([Qty])*Nz([YourComboBox].Column(2),0)

By the way, you should not try to save this calculated quantity in a table
field. Calculate it as need either in a form or report control, or in a
calculated query field.

Sprinks

The more I think about this it is a little more complicated then what I
originally said. I also have to make sure that the following is true/False

For Example: Qty = 30 * 1.25 if Category = Saddles and Received is True.
Handlingtxt = $37.50

I do have a table for the Category already. It lists "Saddle", "Boom",
"Other", "Bags" in it. That is all. So are you saying add another column
for example:

Category Cost
Saddle 1.25
Boom 35.00
Bags 0.00
Etc....

Tony,

Amy makes a good point--if you have more than one multiplier, it would be
better to have it in a table. You don't need another table, however; you
could add a field to the existing Category table, called, e.g., Multiplier.
Then include this as a column in the Category combo box, and refer to it
using the Column property, which starts numbering from zero. So if the
Multiplier is the third column, e.g., the expression becomes:

=Nz([Qty])*Nz([YourComboBox].Column(2))

Nz will avoid an error if Qty or the Multiplier are zero.

Sprinks

I have a subform that has the Following Fields:

Date
Equipment = combobox
Qty = txtbox
Recieved = checkbox
Shipped = checkbox
Category = Combobox
Comments = memo

I have a text box that I would like to show me my handling cost per
equipment piece. But it is based on Category value. The value in the
Category field must say Saddles. I can't figure out how to write it.

For Example: Qty = 30 * 1.25 if Category = Saddles.
Handlingtxt = $37.50

Can some one help?