Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Huge IF statement I suspect should be a lot simpler

Status
Not open for further replies.
SeadnaS

SeadnaS

Programmer
May 30, 2011
214
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.
 
Sort by date Sort by votes
USE Select Case - End Select
Example

sizecombo = "LAC_BP_13025"
PASSFAIL1 = "NONE"
AVG_3SIGM = 18

Select Case sizecombo
Case Is = "LAC_BP_13010", "LAC_BP_13015", "LAC_BP_13020", "LAC_BP_13025"
Select Case AVG_3SIGM
Case Is = 18
PASSFAIL1 = "Valuex"
Case Is = 17.5
PASSFAIL1 = "Value"
Case Is = 17
PASSFAIL1 = "Value"
Case Is = 16.5
PASSFAIL1 = "Value"
Case Is = 16
PASSFAIL1 = "Value"
Case Is = 15.5
PASSFAIL1 = "Value"
Case Is = 15
PASSFAIL1 = "Value"
Case Else
PASSFAIL1 = "Other Value"
End Select
End Select
 
Upvote 0 Downvote
Hmm not sure i understand that fully. Will try and implement it. So all i need to change is "Value" to "Pass" or "Fail" am i correct?
 
Upvote 0 Downvote
It will be more easy if you initialize [PASSFAIL1.value] as "Pass"
in the start of Select Case

then Check through Select only possibily of 'Fail' and change the value as "Fail" in that checked.

If I get the time, I will right the complete code
 
Upvote 0 Downvote
Would really like to be able to do it that way, looks like its going to be six times longer than what i anticipated originally. Is there somewhere online that gives a good explanation of how to use Select Case for my needs?
 
Upvote 0 Downvote


VB Help maybe?

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
I almost have it finished the long way.
 
Upvote 0 Downvote

Since your Pass/Fail criteria varies with the test, add it to the table that you use for your ComboBox RowSource and make it a column in your ComboBox. Then you only need to use:
Code: 
PassFail1.Value = "Fail"
Select Case AVG_3SIGM.Value >= Me.SizeCombo.Column(X)
   Case True
      PassFail1.Value = "Pass"
End Select
where X = the (zero-based) Column Number of the Fass/Fail Criteria.
 
Upvote 0 Downvote
I earlier gave u the right syntax of using Select Case, now its depend on logic to simplify the code for checking value.

I suggested Select Case as through this u dont have to use multiple operators like "=><" and "Or/And"

And In your case, many numbers of comparison required multiple and repeated IF, OR, AND, =, and Field Name which was making code longer.

Now check following simplified code for your need from the best of my knowledge.

It may required some touch.. hope u can do that...
let me know.the result..
Code: 
PASSFAIL1.Value = "Fail"
Select Case Me.AVG_3SIGM.Value
    Case Is = 18
        Select Case Me.sizecombo
            Case Is = "LAC_BP_13010", "LAC_BP_20010", "LAC_BP_20015", "LAC_BP_20020"
            PASSFAIL1.Value = "Pass"
        End Select

    Case Is = 17.5
        Select Case Me.sizecombo
            Case Is = "LAC_BP_22515", "LAC_BP_22520", "LAC_BP_25010", "LAC_BP_25015", "LAC_BP_25020", "LAC_BP_25015", "LAC_BP_25020", "LAC_BP_25030"
            PASSFAIL1.Value = "Pass"
        End Select

    Case Is = 17
        Select Case Me.sizecombo
            Case Is = "LAC_BP_27515", "LAC_BP_27520", "LAC_BP_30010", "LAC_BP_30015", "LAC_BP_30020", "LAC_BP_30020"
            PASSFAIL1.Value = "Pass"
        End Select

    Case Is = 16.5
        Select Case Me.sizecombo
            Case Is = "LAC_BP_32515", "LAC_BP_32520"
            PASSFAIL1.Value = "Pass"
        End Select

    Case Is = 15.5
        Select Case Me.sizecombo
            Case Is = "LAC_BP_35015", "LAC_BP_35020", "LAC_BP_35030", "LAC_BP_37515"
            PASSFAIL1.Value = "Pass"
        End Select

    Case Is = 15
        Select Case Me.sizecombo
            Case Is = "LAC_BP_40015", "LAC_BP_40020"
            PASSFAIL1.Value = "Pass"
        End Select

    Case Else
        PASSFAIL1.Value = "Fail"
 End Select
 
Upvote 0 Downvote
I would not hardwire this. Data belongs in tables and not in code. Look at what Gammachaser suggests, it would be a whole lot more flexible and easier to debug. Your code could be even simpler.
Code: 
PassFail1.Value = "Fail"
If AVG_3SIGM.Value >= Me.SizeCombo.Column(X) then PassFail1.Value = "Pass"
 
Upvote 0 Downvote
How are ya SeadnaS . . .

A straight up [blue]Select Case[/blue] statement turned out pretty compact:
Code: 
[blue]   Select Case Me.SizeCombo
      Case "LAC_BP_13010", "LAC_BP_20010", "LAC_BP_20015", "LAC_BP_20020"
         If AVG_3SIGM.Value >= 18 Then PassFail1.Value = "Pass"
      Case "LAC_BP_22515", "LAC_BP_22520", "LAC_BP_25010", "LAC_BP_25015", _
            "LAC_BP_25020", "LAC_BP_25030"
         If AVG_3SIGM.Value >= 17.5 Then PassFail1.Value = "Pass"
      Case "LAC_BP_27515", "LAC_BP_27520", "LAC_BP_30010", "LAC_BP_30015", _
           "LAC_BP_30030"
         If AVG_3SIGM.Value >= 17 Then PassFail1.Value = "Pass"
      Case "LAC_BP_32515", "LAC_BP_32520"
         If AVG_3SIGM.Value >= 16.5 Then PassFail1.Value = "Pass"
      Case "LAC_BP_35015", "LAC_BP_35020", "LAC_BP_35030", "LAC_BP_37515"
         If AVG_3SIGM.Value >= 15.5 Then PassFail1.Value = "Pass"
      Case "LAC_BP_40015", "LAC_BP_40020"
         If AVG_3SIGM.Value >= 15 Then PassFail1.Value = "Pass"
      Case Else
         MsgBox "Size Not Detected!"
   End Select[/blue]
I'd stay with select case as it allows you to easily add other criteria.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote

I like Select Case for a lot of reasons. I also like putting the values in a table instead of coding them for a lot of reasons, mostly because it is easier for my clients to maintain and, while I like getting call backs from customers, I want them to be for more functionality and not because my program is obsolete due to poor design.

The most elegant code I have found using the suggestion I made about the ComboBox column is:
Code: 
PassFail1 = Mid("PassFail", 1 - 4*(AVG_3SIGM < SizeCombo.Column(1)),4)
Having said that, I am not sure I would use that in a program that someone else might have to modify someday, since it is not the clearest statement in the world. But it sure is compact.
 
Upvote 0 Downvote
would this be work? can you guys have a look at this code to see if its ok?

Select Case Me.sizecombo

Case "LAC_BP_40015", "LAC_BP_40020", "LAOH_BP_40015", "LAOH_BP_40020", "LAX_BP_40015", "LAX_BP_40020"
If AVG_3SIGM.Value >= 15 Then PASSFAIL1.Value = "Pass"

Case "LAC_BP_13010", "LAC_BP_20010", "LAC_BP_20015", "LAC_BP_20020", "LAOH_BP_13010", "LAOH_BP_20010", "LAOH_BP_20015", "LAOH_BP_20020", "LAX_BP_13010", "LAX_BP_20010", "LAX_BP_20015", "LAX_BP_20020"
If AVG_3SIGM.Value >= 18 Then PASSFAIL1.Value = "Pass"

Case "LAC_BP_22515", "LAC_BP_22520", "LAC_BP_25010", "LAC_BP_25015", "LAC_BP_25020", "LAC_BP_25030", "LAOH_BP_22515", "LAOH_BP_22520", "LAOH_BP_25010", "LAOH_BP_25015", "LAOH_BP_25020", "LAOH_BP_25030", "LAX_BP_22515", "LAX_BP_22520", "LAX_BP_25010", "LAX_BP_25015", "LAX_BP_25020", "LAX_BP_25030"
If AVG_3SIGM.Value >= 17.5 Then PASSFAIL1.Value = "Pass"

Case "LAC_BP_27515", "LAC_BP_27520", "LAC_BP_30010", "LAC_BP_30015", "LAC_BP_30020", "LAC_BP_30030", "LAOH_BP_27515", "LAOH_BP_27520", "LAOH_BP_30010", "LAOH_BP_30015", "LAOH_BP_30020", "LAOH_BP_30030", "LAX_BP_27515", "LAX_BP_27520", "LAX_BP_30010", "LAX_BP_30015", "LAX_BP_30020", "LAOH_BP_30030"
If AVG_3SIGM.Value >= 17 Then PASSFAIL1.Value = "Pass"

Case "LAC_BP_32515", "LAC_BP_32520", "LAOH_BP_32515", "LAOH_BP_32520", "LAX_BP_32515", "LAX_BP_32520"
If AVG_3SIGM.Value >= 16.5 Then PASSFAIL1.Value = "Pass"

Case "LAC_BP_35015", "LAC_BP_35020", "LAC_BP_35030", "LAC_BP_37515", "LAOH_BP_35015", "LAOH_BP_35020", "LAOH_BP_35030", "LAOH_BP_37515", "LAX_BP_35015", "LAX_BP_35020", "LAX_BP_35030", "LAX_BP_37515"
If AVG_3SIGM.Value >= 15.5 Then PASSFAIL1.Value = "Pass"

Case "Like 'LAH_BP_45*'", "VIV_BP_4008", "VIV_BP_40012", "VIV_BP_40015", "VIV_BP_40018", "VIV_BP_40021", "VIV_BP_40025", "VIV_BP_45008", "VIV_BP_45012", "VIV_BP_45015", "VIV_BP_45018", "VIV_BP_45021", "VIV_BP_45025"
If AVG_3SIGM.Value >= 20 Then PASSFAIL1.Value = "Pass"

Case "Like 'LAH_BP_2*'", "Like 'LAH_BP_3*'", "LAH_BP_40008", "LAH_BP_40010", "LAH_BP_40015", "LAH_BP_40018"
If AVG_3SIGM.Value >= 22 Then PASSFAIL1.Value = "Pass"

End Select
 
Upvote 0 Downvote


PassFail1 = Mid("PassFail", 1 - 4*(AVG_3SIGM < SizeCombo.Column(1)),4)

can u explain that to me please gamma? im trying to do a very similar thing as Seadna!
 
Upvote 0 Downvote

joebox888 --

That is why I would avoid putting it in code someone else has to modify! It is not very intuitive. But...

Code: 
PassFail1 = Mid("PassFail", 1 - 4*(AVG_3SIGM < SizeCombo.Column(1)),4)
This works because False = 0 and True = -1, allowing you to use them in mathematical functions.

So, if AVG_3SIGM < SizeCombo.Column(1) you get -1 (True) as a result. You multiply by 4 and get -4, and since subtracting a negative is the same as adding, 1 - (-4) = 5 which is the number used for the starting position in the Mid() function. That means that you end up with Mid("PassFail", 5, 4) which returns "Fail" because it is the 4 characters of "PassFail" starting in the 5th position.

If the opposite is the case (AVG_3SIGM >= SizeCombo.Column(1)) the result would be 0 (False), and the resulting math would produce '1 - (4 * 0)' or 1 - 0 = 1 and the the function would be Mid("PassFail", 1, 4) producing the 4 character string starting in the first position ("Pass")

Clear? Maybe not. But it works. I use the fact that False = 0 and (x * 0) = 0 quite a bit to eliminate code steps, particularly if the code is part of a loop or called very frequently.
 
Upvote 0 Downvote

Gammachaser said:
False = 0 and True = -1
Click to expand...
Actually, False = 0 and True <> 0
Any value - other than 0 - is TRUE

It is handy to know when any math calculations can be used for True/False outcome.

I used it often in something like:
[tt]
SomeControl.Enable = rst.RecordCount
or
SomeControl.Visible = rst.RecordCount[/tt]

Have fun.

---- Andy
 
Upvote 0 Downvote

Andy --

You are coming at it from the opposite side.

What I said was that True = -1... what you are saying is that any number = True. Big difference. Any calculation that produces a non-0 number can be interpreted as True, but when an expression evaluates as True you always get -1.

So, you are correct in what you say, but you are not saying the same thing I am. You are looking at the other side of the '='.

False = 0 and True = -1.

0 = False and AnyOtherNumber = True

Both correct, but used for different purposes.
 
Upvote 0 Downvote

Gammachaser,

True ( = -1 :) ), you are right. I didn't try to prove you wrong. Quiet the opposite.

I was just trying to point out the TRUE <> 0 which comes very handy in programming


Have fun.

---- Andy
 
Upvote 0 Downvote
That way looks so easy however im gonna stay away from it if that's ok(just dont really get it and also trying to stay away from a new column)..
I had this working yesterday however now it seems not to be!
Is it okay to have an IF statement like this?


If Me.typecombo = "BP" Then
If Me.sizecombo = "13010" Or "20010" Or "20015" Or "20020" & Me.classcombo = "LAC" Then

If [AVG_3SIGM].Value >= 18 Then
PASSFAIL.Value = "Pass"
Else: PASSFAIL.Value = "Fail"
End If

Else
If Me.sizecombo = "22515" Or "22520" Or "25010" Or "25015" Or "25020" Or "25030" & Me.classcombo = "LAC" Then
If [AVG_3SIGM].Value >= 17.5 Then
PASSFAIL.Value = "Pass"
Else: PASSFAIL.Value = "Fail"
End If
//just a snippet!

This doesnt seem to move through them at all it just sets the passfail mark as 18...
any ideas?
Thanks
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

M
  • Question
display a calendar with appointments in body of html email with access vba
Replies
1
Views
334
SoftwareRT
SoftwareRT
Moss100
  • Locked
  • Question
Vba code I can call from a query to reformat text
Replies
8
Views
226
Moss100
Moss100
Moss100
  • Locked
  • Question
Remove everything including the comma from the last comma of a string. 1
Replies
10
Views
339
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Access prompts &quot;open or save&quot; when I preview an excel file 1
Replies
1
Views
113
combo
combo
aldi07
  • Locked
  • Question
update a front-end database with a new release using vba
Replies
5
Views
176
aldi07
aldi07

Part and Inventory Search

Sponsor

Back
Top