Entering Formulas in Access Forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a field that calculates the value of an account. In
order to do this I need three fields... the employee total, a percentage of
professionals, and the cost per professional. However, the cost per
professional depends on how many total employees there are...so if there are
1,000 total employees then the cost per employee is different then if there
are 1,000+.

I want the field to calcualte: Total Employee*Percent Professional*Cost Per
Employee

Ideally I would like it to be:

If Total Employee >=1,000, Multiply (Total Employee)*(Percent
Professional)*(Cost Per Employee1) Or If Total Employee < 1,000, Multiply
(Total Employee)*(Percent Professional)*(Cost Per Employee2)

But I can't figure it out

I've tried using the help function but it's not helping. Please any help as
soon as possible would be greatly appreciated!!!
 
BeckBuck,

Do you mean that Total Employee, Percent Professional, Cost Per
Employee1, and Cost Per Employee2, are all fields within the record
source of the form? If so, I think you can put this into the Control
Source of an unbound textbox on the form...
=[Total Employee]*[Percent Professional]*IIf([Total
Employee]<1000,[Cost Per Employee2],[Cost Per Employee1])
 
I am trying to create a field that calculates the value of an account. In
order to do this I need three fields... the employee total, a percentage of
professionals, and the cost per professional. However, the cost per
professional depends on how many total employees there are...so if there are
1,000 total employees then the cost per employee is different then if there
are 1,000+.

I want the field to calcualte: Total Employee*Percent Professional*Cost Per
Employee

Ideally I would like it to be:

If Total Employee >=1,000, Multiply (Total Employee)*(Percent
Professional)*(Cost Per Employee1) Or If Total Employee < 1,000, Multiply
(Total Employee)*(Percent Professional)*(Cost Per Employee2)

But I can't figure it out

I've tried using the help function but it's not helping. Please any help as
soon as possible would be greatly appreciated!!!
Click to expand...

The IIF() function will help here:

[Total Employee] * [Percent Professional] * IIF([Total Employee] >=
1000, [Cost Per Employee1], [Cost Per Employee2])

The way IIF works is that it takes three arguments. If the first one
is TRUE, it returns the second; if FALSE, the third.

John W. Vinson[MVP]
 
Back
Top