Combo Box Addition

[John]

I am trying to set-up a job costing sheet. I have 1 combo box doing a simple
hours * rate calculation. I have 15 different "hour" boxes and 15 different
"rate" boxes. All of these boxes have a "" default value. I then have 15
different "Total Cost" boxes doing the rate * hours calculation. These combo
boxes utilize the IIf() statement so that I do not have a row of $0.00. At
the bottom of the form, I have a "Labor Totals" combo box. This box is to add
all of the "Total Cost" boxes that have a value in them. My problem is that
this form does not work with the IIf () statement in the "Total Cost" combo
box. It just has a "" value. If I remove the IIf() statement, this box
calculates fine. Is there any way to have a running sum box and utilize the
IIf statement at the same time?

 

[BruceM]

A combo box has a down arrow, and is used to select items from a drop-down
list. I would expect your calculation is in a text box.
Are your 15 different hour boxes and 15 different rate boxes all different
from each other before you begin, or are they the same box repeated 15 times?
Without knowing anything about the IIf statement it is impossible to say why
it isn't working as expected. What do you want the Total box to do? What do
you hope to accomplish with the IIf statement?

 

[Sprinks]

Hi, John.

I think you mean "textboxes", not combo boxes. Combo boxes are utilized to
allow the user to pick from a set of predefined values.

Rather than the IIf statement, use the Nz function to convert a null value
to zero:

=Nz([Hours])*Nz([Rate])

Then all should add up correctly.

However, your table structure is not sound. There is a natural one-to-many
relationship between Job and JobDetails. Instead of 15 sets of fields, use a
main form based on Job and a continuous subform based on JobDetails that
enables you to enter any arbitrary number of of rate/hour combinations. A
summary field in the subform footer could then display the total:

=Sum(Nz([Hours])*Nz([Rate]))

Something like:

Jobs
-----------
JobID AutoNumber (Primary Key)
JobName Text
JobStart Date/Time
Manager Integer (Foreign Key to Employees)
....
etc.

JobDetails
-------------
JobDetailID AutoNumber (Primary Key)
JobID Integer (Foreign Key to Jobs)
Hours Single
Rate Currency

Do not include a Total Cost *field* in the table, you can display it in a
form textbox as a calculation, or if you need to print it, as a calculated
field in a query.

Hope that helps.
Sprinks

 

[John]

Bruce,
I apologize, they are text boxes. The IIf() statements checks the "Hours"
and "Rate" text boxes to see if they are blank. If they are, it changes the
"Total Cost" box to "". All of the hour boxes and all of the rate boxes are
seperate from each other [Rate 1], [Rate 2], etc.I utilized the IIf statement
so that I would not have a row of $0.00 in the Total Cost row. I tried to
default the value to "" but that didn't work either. I want the Labor Totals
box to calculate all of the "Total Cost" text boxes that have a value in them.

 

[John]

Bruce,
I also utilized the IIf() statement to get rid of the div by zero error
message. Thanks for all your help.

John

 

[John]

Sprinks,

That worked like a charm !!!! Thank You Very Much !!!!!

John

Hi, John.

I think you mean "textboxes", not combo boxes. Combo boxes are utilized to
allow the user to pick from a set of predefined values.

Rather than the IIf statement, use the Nz function to convert a null value
to zero:

=Nz([Hours])*Nz([Rate])

Then all should add up correctly.

However, your table structure is not sound. There is a natural one-to-many
relationship between Job and JobDetails. Instead of 15 sets of fields, use a
main form based on Job and a continuous subform based on JobDetails that
enables you to enter any arbitrary number of of rate/hour combinations. A
summary field in the subform footer could then display the total:

=Sum(Nz([Hours])*Nz([Rate]))

Something like:

Jobs
-----------
JobID AutoNumber (Primary Key)
JobName Text
JobStart Date/Time
Manager Integer (Foreign Key to Employees)
...
etc.

JobDetails
-------------
JobDetailID AutoNumber (Primary Key)
JobID Integer (Foreign Key to Jobs)
Hours Single
Rate Currency

Do not include a Total Cost *field* in the table, you can display it in a
form textbox as a calculation, or if you need to print it, as a calculated
field in a query.

Hope that helps.
Sprinks

I am trying to set-up a job costing sheet. I have 1 combo box doing a simple
hours * rate calculation. I have 15 different "hour" boxes and 15 different
"rate" boxes. All of these boxes have a "" default value. I then have 15
different "Total Cost" boxes doing the rate * hours calculation. These combo
boxes utilize the IIf() statement so that I do not have a row of $0.00. At
the bottom of the form, I have a "Labor Totals" combo box. This box is to add
all of the "Total Cost" boxes that have a value in them. My problem is that
this form does not work with the IIf () statement in the "Total Cost" combo
box. It just has a "" value. If I remove the IIf() statement, this box
calculates fine. Is there any way to have a running sum box and utilize the
IIf statement at the same time?