Huge IF statement I suspect should be a lot simpler

[SeadnaS]

Hi,

I'm a beginner at access and vba.

I have a box on my form that displays pass or fail depending on whether another boxes value is greater or less than a certain value.

I feel like i should be able to put in a few OR's in here to make it shorter. This is the only way i could get this code to work.



If Me.sizecombo = "LAC_BP_13010" Then
If AVG_3SIGM.Value >= 18 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_20010" Then
If AVG_3SIGM.Value >= 18 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_20015" Then
If AVG_3SIGM.Value >= 18 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_20020" Then
If AVG_3SIGM.Value >= 18 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_22515" Then
If AVG_3SIGM.Value >= 17.5 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_22520" Then
If AVG_3SIGM.Value >= 17.5 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_25010" Then
If AVG_3SIGM.Value >= 17.5 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_25015" Then
If AVG_3SIGM.Value >= 17.5 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_25020" Then
If AVG_3SIGM.Value >= 17.5 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_25030" Then
If AVG_3SIGM.Value >= 17.5 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_27515" Then
If AVG_3SIGM.Value >= 17 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_27520" Then
If AVG_3SIGM.Value >= 17 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_30010" Then
If AVG_3SIGM.Value >= 17 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_30015" Then
If AVG_3SIGM.Value >= 17 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_30020" Then
If AVG_3SIGM.Value >= 17 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_30030" Then
If AVG_3SIGM.Value >= 17 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_32515" Then
If AVG_3SIGM.Value >= 16.5 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_32520" Then
If AVG_3SIGM.Value >= 16.5 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_35015" Then
If AVG_3SIGM.Value >= 15.5 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_35020" Then
If AVG_3SIGM.Value >= 15.5 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_35030" Then
If AVG_3SIGM.Value >= 15.5 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_37515" Then
If AVG_3SIGM.Value >= 15.5 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_40015" Then
If AVG_3SIGM.Value >= 15 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If

Else
If Me.sizecombo = "LAC_BP_40020" Then
If AVG_3SIGM.Value >= 15 Then
PASSFAIL1.Value = "Pass"
Else: PASSFAIL1.Value = "Fail"
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If


Now, should i be able to work it so that it says If Me.sizecombo = "something" Or "something else" etc etc because i tried that and it didnt work. I'm obviously doing something wrong here.

 

[PHV]

Replace this:
If Me.sizecombo = "13010" Or "20010" Or "20015" Or "20020" & Me.classcombo = "LAC" Then
with this:
If (Me.sizecombo = "13010" Or Me.sizecombo = "20010" Or Me.sizecombo = "20015" Or Me.sizecombo = "20020") And Me.classcombo = "LAC" Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886