IF Then statement

[Guest]

I'd like to create an if-then statment for calculating hourly/salary yearly
totals. I have a field that holds a dollar amount that is either a monthly
amount or an hourly rate [PayRate] and another field that determines whether
the employee is hourly or salary [PayType]. What I need to do is calculate
the yearly (estimated) pay for any given employee. So basically it goes: If
[PayType]=Salary Then [PayRate]*12 or If [PayType]=Hourly Then
[PayRate]*2080 (2,080 hours per year based on a 40 hour work week). I want
this calculation to land in a text box on a form [txtPayCalc]. I'm just not
sure of the exact syntax to use for this calculation and where the code
should go (After Update Event maybe?). Still kinda new at the coding thing.

 

[Guest]

Since this value is calculated, it should NOT be stored in a field in a
table. It should be calculated each time the record with the information is
displayed. The text box that displays it should remain blank for new records
until all the controls used in the calculation are filled in.
The technique I would use would be to create a function that will calculate
the value and use the function in the control source property of the text box
you want it display in. I would also put the function in a Standard module
as a Public function so it can be used for other forms and reports that may
have to display the value.

Here is how the function might look:
(I am replacing the names you posted, because they are obviously field names
in your table and field names and control names should not be the same. When
they are, it make the code hard to read and in rare cases can confuse Access)

PubLic Function AnnualPayEstimated(dblPayRate As Double, _
strPayType As String) As Variant

If IsNull(dblPayRate) Or IsNull(dblPayType) Then
AnnualPayEstimate = Null
ElseIf strPayType = "Hourly" Then
AnnualPayEstimate = dblPayRate * 2080
ElseIf strPayType = "Salary" Then
AnnualPayEstimate = dblPayRate * 12
End If

End Function

Now to call it from the control source of txtPayCalc:

= AnnualPayEstimate(Me.txtPayRate, Me.txtPayType)

 

[Guest]

Ok , I created the module and pointed the control source of [txtPayCalc] to
the module. I am getting a #Name? error in the [txtPayCalc] control. Do I
need to add code to pull the data entered into [txtPayRate] and [txtPayType]
into the variables defined in the module (dblPayRate, strPayType)? Like
me.txtPayRate = dblPayRate?

Since this value is calculated, it should NOT be stored in a field in a
table. It should be calculated each time the record with the information is
displayed. The text box that displays it should remain blank for new records
until all the controls used in the calculation are filled in.
The technique I would use would be to create a function that will calculate
the value and use the function in the control source property of the text box
you want it display in. I would also put the function in a Standard module
as a Public function so it can be used for other forms and reports that may
have to display the value.

Here is how the function might look:
(I am replacing the names you posted, because they are obviously field names
in your table and field names and control names should not be the same. When
they are, it make the code hard to read and in rare cases can confuse Access)

PubLic Function AnnualPayEstimated(dblPayRate As Double, _
strPayType As String) As Variant

If IsNull(dblPayRate) Or IsNull(dblPayType) Then
AnnualPayEstimate = Null
ElseIf strPayType = "Hourly" Then
AnnualPayEstimate = dblPayRate * 2080
ElseIf strPayType = "Salary" Then
AnnualPayEstimate = dblPayRate * 12
End If

End Function

Now to call it from the control source of txtPayCalc:

= AnnualPayEstimate(Me.txtPayRate, Me.txtPayType)
--
Dave Hargis, Microsoft Access MVP

I'd like to create an if-then statment for calculating hourly/salary yearly
totals. I have a field that holds a dollar amount that is either a monthly
amount or an hourly rate [PayRate] and another field that determines whether
the employee is hourly or salary [PayType]. What I need to do is calculate
the yearly (estimated) pay for any given employee. So basically it goes: If
[PayType]=Salary Then [PayRate]*12 or If [PayType]=Hourly Then
[PayRate]*2080 (2,080 hours per year based on a 40 hour work week). I want
this calculation to land in a text box on a form [txtPayCalc]. I'm just not
sure of the exact syntax to use for this calculation and where the code
should go (After Update Event maybe?). Still kinda new at the coding thing.

 

[fredg]

I'd like to create an if-then statment for calculating hourly/salary yearly
totals. I have a field that holds a dollar amount that is either a monthly
amount or an hourly rate [PayRate] and another field that determines whether
the employee is hourly or salary [PayType]. What I need to do is calculate
the yearly (estimated) pay for any given employee. So basically it goes: If
[PayType]=Salary Then [PayRate]*12 or If [PayType]=Hourly Then
[PayRate]*2080 (2,080 hours per year based on a 40 hour work week). I want
this calculation to land in a text box on a form [txtPayCalc]. I'm just not
sure of the exact syntax to use for this calculation and where the code
should go (After Update Event maybe?). Still kinda new at the coding thing.

So the only possible choices are Salary or Hourly?
Add an Unbound text control to your form.
Set it's control source property to:

=IIf([Pay Type] = "Salary",[PayRate] * 12,[PayRate]* 2080)

This calculated value is not to be stored in any table. Any time you
need the calculated result, on a form or in a report, simply
re-calculate it, as above.

 

[Guest]

Be sure the module and the function do not have the same name.
notice how I called the function from the control source. It needs the =
sign and the name of the function, and the control names for the PayRate and
PayType. Be sure the data types are correct. I just guessed at those.

--
Dave Hargis, Microsoft Access MVP

Ok , I created the module and pointed the control source of [txtPayCalc] to
the module. I am getting a #Name? error in the [txtPayCalc] control. Do I
need to add code to pull the data entered into [txtPayRate] and [txtPayType]
into the variables defined in the module (dblPayRate, strPayType)? Like
me.txtPayRate = dblPayRate?

Since this value is calculated, it should NOT be stored in a field in a
table. It should be calculated each time the record with the information is
displayed. The text box that displays it should remain blank for new records
until all the controls used in the calculation are filled in.
The technique I would use would be to create a function that will calculate
the value and use the function in the control source property of the text box
you want it display in. I would also put the function in a Standard module
as a Public function so it can be used for other forms and reports that may
have to display the value.

Here is how the function might look:
(I am replacing the names you posted, because they are obviously field names
in your table and field names and control names should not be the same. When
they are, it make the code hard to read and in rare cases can confuse Access)

PubLic Function AnnualPayEstimated(dblPayRate As Double, _
strPayType As String) As Variant

If IsNull(dblPayRate) Or IsNull(dblPayType) Then
AnnualPayEstimate = Null
ElseIf strPayType = "Hourly" Then
AnnualPayEstimate = dblPayRate * 2080
ElseIf strPayType = "Salary" Then
AnnualPayEstimate = dblPayRate * 12
End If

End Function

Now to call it from the control source of txtPayCalc:

= AnnualPayEstimate(Me.txtPayRate, Me.txtPayType)
--
Dave Hargis, Microsoft Access MVP

I'd like to create an if-then statment for calculating hourly/salary yearly
totals. I have a field that holds a dollar amount that is either a monthly
amount or an hourly rate [PayRate] and another field that determines whether
the employee is hourly or salary [PayType]. What I need to do is calculate
the yearly (estimated) pay for any given employee. So basically it goes: If
[PayType]=Salary Then [PayRate]*12 or If [PayType]=Hourly Then
[PayRate]*2080 (2,080 hours per year based on a 40 hour work week). I want
this calculation to land in a text box on a form [txtPayCalc]. I'm just not
sure of the exact syntax to use for this calculation and where the code
should go (After Update Event maybe?). Still kinda new at the coding thing.