If statement multiple conditions

[Guest]

I am trying to write an If statement for a field in access 2003. I have three
fields testbox1 and textbox2 are combo boxes. The third field (textbox3) is
based on the combination of the first two fields. Textbox1 has five choices
and textbox2 has four choices making twenty posible combinations.

If textbox1 = 1 and textbox2 = a then
textbox3 = .20
If textbox1 = 1 and textbox2 = b then
textbox3 = .30
and so on ....
As you can see I am new at this so any help would be graeatly appreciated.
Thanks

 

[Jason Lepack]

Create a new table with three columns.

tbl_tb3_lookup:
tb1_value
tb2_value
tb3_value

Put in all the possible combinations of tb1 and tb2 and the expected
value for tb3.

Then you can set the value by using a query to lookup the expected
value of textbox3.

Cheers,
Jason Lepack

 

[Guest]

Thanks Jason for your help. I have a question. Am I supposed to put the value
of textbox3 in the table? In some inhstances the value can be between two
values (.40 and .80). If so would this create aproblem/ Thanks

 

[Jason Lepack]

In the solution I posted above, if the value in text box 1 is
tb1_value and the value in text box 2 is tb2_value then tb3_value gets
put into text box 3.

I don't understand your question.

Cheers,
Jason Lepack

 

[Guest]

Hi Jason and thanks for taking the time to help. I don't think that I
accurately stated my problem so I'll try again. I creating a merit increase
database. The merit increase is based on the employee's performance rating
and their quartile. The merit increase is percent which is then multiplied
times their pay rate. There are 5 values for performance rating and four
values for quartile. The merit increase has to be within a certain range.
I've included part of the pseudo code for this. Thanks for your patience and
I apologize for not being more precise in stating my problem.

If PerfRate = 5 and Qrtle = 1 then
merit increase = 5-7%
Else
If PerfRate = 5 and Qrtle = 2 then
merit increase = 4-6%
Else
If PerfRate = 5 and Qrtle = 3 then
merit increase = 0-4%
Else
If PerfRate = 5 and Qrtle = 4 then
merit increase = 0-3%
End If

 

[Jason Lepack]

What do you intend to do with the merit_increase variable? If you
just intend to display it then make tb3_value into text and output
it. If you need to do some sort of calculation then you'll have to
explain that calculation.

 

[Guest]

The merit increase percentage will be multiplied times the current pay rate
to give the amount of the increase which will be added to the current pay
rate to produce the new pay rate. i hope thhis makes sense. Thanks

 

[Jason Lepack]

Then I guess you need two columns for your merit increase. One for
the maximum and one for the minimum.

 

[John W. Vinson]

The merit increase percentage will be multiplied times the current pay rate
to give the amount of the increase which will be added to the current pay
rate to produce the new pay rate. i hope thhis makes sense. Thanks

What is the product of $34,500 multiplied by 4-6%?

In other words, your IF statement does not provide the information needed to
do the calculation.

John W. Vinson [MVP]

 

[Guest]

What I trying to do is restrict the value that a supervisor can enter in the
merit increase percentage field to a range, e.g. 4-6%, based on the
quartile and performance rating of the employee being evaluated. I understand
your question so is it possible to use if statements to limit field values or
would I have to crease a seperate field for each range of values? Thanks

 

[John W. Vinson]

What I trying to do is restrict the value that a supervisor can enter in the
merit increase percentage field to a range, e.g. 4-6%, based on the
quartile and performance rating of the employee being evaluated. I understand
your question so is it possible to use if statements to limit field values or
would I have to crease a seperate field for each range of values? Thanks

For a range, it's much simpler if you use two fields for the low end and the
high end of the range. These fields should not be enabled on the form where
the supervisor enters the increase (actually this will take care of itself if
they are calculated fields, or based on a DLookUp); you can put code in the
BeforeUpdate event of the percent increase textbox on the Form to cancel the
update if it's not in range:

Private Sub txtIncreasePct_BeforeUpdate(Cancel as Integer)
If Me.txtIncreasePct < Me.txtLowEnd _
Or Me.txtIncreasePct > Me.txtHighEnd Then
MsgBox "Increase must be within stated range", vbOKOnly
Cancel = True
Me.txtincreasePct.Undo
End If
End Sub

John W. Vinson [MVP]

 

[Guest]

Thanks for all your help
--
tm

For a range, it's much simpler if you use two fields for the low end and the
high end of the range. These fields should not be enabled on the form where
the supervisor enters the increase (actually this will take care of itself if
they are calculated fields, or based on a DLookUp); you can put code in the
BeforeUpdate event of the percent increase textbox on the Form to cancel the
update if it's not in range:

Private Sub txtIncreasePct_BeforeUpdate(Cancel as Integer)
If Me.txtIncreasePct < Me.txtLowEnd _
Or Me.txtIncreasePct > Me.txtHighEnd Then
MsgBox "Increase must be within stated range", vbOKOnly
Cancel = True
Me.txtincreasePct.Undo
End If
End Sub

John W. Vinson [MVP]