2 questions.....probably simple for ya'll

[denileigh]

I am still learning and I have 2 questions.

First...I have a table called company rates which will be used to calculate time and materials. In the material field, I need to enter .10 for 10 percent. When I enter .10 the field changes back to zero. I have it set to long interger. Is this wrong?

Also, I am working on the form that will calculate all of this. I have a field that needs to look in a flex price field. If there is a number in the flex price than that is the labor, if not, it needs to multiply the estimated time (Text43) times the quantity, times the CurrentLaborRate contained in the CompanyRates subform. I can't seem to get it right...


=IIf([FlexPrice]>1,[FlexPrice],[Text43]*[Quantity]*[frmCompanyRates.CurrentLaborRate])

I know it is probably something simple but I can't figure it out. Can anyone help?

THANKS SO MUCH!

 

[RuralGuy]

Integers can *only* store whole numbers! You need to use Single, Double or Currency to record a decimal value. As for the SubForm issue, here's a link you might find useful: Refer to Form and Subform properties and controls Post back if you need additional assistance.

 

[denileigh]

Okay - I have it calculating okay however, for some reason now...

a) the calculated value is not passing to the table
b) when I try to calculate the total labor =Sum([Labor]), I am getting an error

 

[RuralGuy]

You should *not* store calculated values in a table but rather calulate them each time you need that value. Agregate functions like Sum() need to be in the footer of your form. Is that where you put the control?

 

[denileigh]

Yes it was. Hmmmm.....what I need to do is get a labor total in that field which is calculates so I can pass it to an append query and then export it ina text format to pull into a customers system. I also need material totals, etc.

If it won't pass a calculated total to the table...how do you suggest I do that?

 

[RuralGuy]

I'm afraid you lost me after the second left turn.

I would need to know more about the structure of your tables and forms to give more precise advice. There are exceptions to the storing of calculation rule.

 

[denileigh]

Hmmm...what I need to do is have a from that allows me to key in the time, materials, outside services and if need, a flex time amount.

The labor needs to calculate as time /60 * current labor rate plus outside services times 10%. The material is the material cost * 40% upcharge.

I need the labor and materials to store in the table along with the net amount (labor + materials).

If I can make it calculate and store...I can get the rest.

Thanks for your help! Is there a way to do this?

 

[RuralGuy]

It can all be accomplished but it sounds like we are not using this as a relational database, which is ok. What fields are in your table and what controls are on your form? Is your form bound to the query/table?

 

[denileigh]

I'm not sure...still new here. Does this help?

http://www.montgomerygirlscouts.org/GATX.zip

 

[denileigh]

P.S. I tried getting it to calculate and store in a query and it was calculating okay but then the combo boxes on the form quit working. *sniff*

 

[RuralGuy]

Your ComboBoxes in the tblJobDetailsub form seem to be bound to fields that are not in the bound query of the form. In which form are you attempting to do the calculations you are having trouble with?

 

[denileigh]

Okay - I'm confused.....should I be running the form from a query that does the calculations or from the table?

 

[RuralGuy]

I *always* use queries. To repeat myself:

Your ComboBoxes in the tblJobDetailsub form seem to be bound to fields that are not in the bound query of the form. In which form are you attempting to do the calculations you are having trouble with?

 

[denileigh]

How do I fix that problem? I am trying to do the calculations in the frmJobDetailSub form.

 

[denileigh]

I tried to re-do the form based on a query that does the calculations and it looked like it worked until I tried to enter data. (See frmJobDetailMain).

How come it won't let me enter data but I can change what it there? I attached a zip file of the db.

 

[denileigh]

Okay - I FINALLY got this functioning for us! YIPPEE! HOWEVER, I have a formatting issue....the labor is calculated in the query like this:

=IIf([FlexTime]>1,[FlexTime],[EstTime]/60*[Quantity]*[LaborRate]+[ExtServ]*[Upcharge]+[ExtService])

This is working GREAT! The problem is the output that will be exported has to be formatted like this (example 91.65) 00009165

How do I include the formatting in the calculation string?

 

[RuralGuy]

Sorry I dropped the ball. Have you figured this out yet? Try:
=Format(IIf([FlexTime]>1,[FlexTime],[EstTime]/60*[Quantity]*[LaborRate]+[ExtServ]*[Upcharge]+[ExtService])*100,"00000000")