Solved IF OR AND statement vba, multiple conditions (1 Viewer)

Jordonjd

Member
Local time
Today, 23:24
Joined
Jun 18, 2020
Messages
96
Hi everyone,

I have a very long If statement which i have realised only applies the "AND" to the last "OR" I have entered.
Basically i have a dropdown box and once the record is complete, depending on the "Move_Reason" certain fields can or cannot have certain data in them.
Some "Move_Reason" have the same requirements

I'm sure there is a way without me having to write each individual condition down for each "Move_Reason"

Im sure i've explained it badly, here is a snippet of x4 "Move_Reason" that should follow the same rules but it only works on "Disassembly"

I understand the OR AND is the reason why disassembly is the only one working properly but it looks like such a long way to have every move reason have an OR AND ?

Code:
If Me.Move_Reason = "Order" Or Me.Move_Reason = "Repair" Or Me.Move_Reason = "ForParts" Or Me.Move_Reason = "Disassembly" And Me.[+/-] = "+" Or _
Me.Move_Reason = "Order" Or Me.Move_Reason = "Repair" Or Me.Move_Reason = "ForParts" Or Me.Move_Reason = "Disassembly" And IsNull(Me.From_Location) Or _
Me.Move_Reason = "Order" Or Me.Move_Reason = "Repair" Or Me.Move_Reason = "ForParts" Or Me.Move_Reason = "Disassembly" And Me.From_Location = "" Or _
Me.Move_Reason = "Order" Or Me.Move_Reason = "Repair" Or Me.Move_Reason = "ForParts" Or Me.Move_Reason = "Disassembly" And IsNull(Me.Ref) Or _
Me.Move_Reason = "Order" Or Me.Move_Reason = "Repair" Or Me.Move_Reason = "ForParts" Or Me.Move_Reason = "Disassembly" And Not IsNull(Me.To_Location) Or _
Me.Move_Reason = "Order" Or Me.Move_Reason = "Repair" Or Me.Move_Reason = "ForParts" Or Me.Move_Reason = "Disassembly" And Me.To_Location <> "" Or _
Me.Move_Reason = "Order" Or Me.Move_Reason = "Repair" Or Me.Move_Reason = "ForParts" Or Me.Move_Reason = "Disassembly" And Me.Ref = "" Then

MsgBox "ERROR! Please check, At least one field is incorrect"

DoCmd.CancelEvent
 
Last edited:

Minty

AWF VIP
Local time
Today, 22:24
Joined
Jul 26, 2013
Messages
10,355
Without understanding the full problem, I think you can simplify the statement to
Code:
If Me.Move_Reason = "Order" Or Me.Move_Reason = "Repair" Or Me.Move_Reason = "ForParts" Or Me.Move_Reason = "Disassembly"  Then
    If Me.[+/-] = "+" Or  IsNull(Me.From_Location) Or  Me.From_Location = "" Or  IsNull(Me.Ref) Or  Not IsNull(Me.To_Location) Or  Me.To_Location <> "" Or  Me.Ref = "" Then
        MsgBox "ERROR! Please check, At least one field is incorrect"
        DoCmd.CancelEvent
    End if
End if
 

Minty

AWF VIP
Local time
Today, 22:24
Joined
Jul 26, 2013
Messages
10,355
Or slightly more elegantly
Code:
SELECT CASE Me.Move_Reason

    CASE "Order","Reason","Repair","ForParts","Disassembly"
        If Me.[+/-] = "+" Or  IsNull(Me.From_Location) Or  Me.From_Location = "" Or  IsNull(Me.Ref) Or  Not IsNull(Me.To_Location) Or  Me.To_Location <> "" Or  Me.Ref = "" Then
                MsgBox "ERROR! Please check, At least one field is incorrect"
                DoCmd.CancelEvent
        End If
  
END SELECT
 

Jordonjd

Member
Local time
Today, 23:24
Joined
Jun 18, 2020
Messages
96
Or slightly more elegantly
Code:
SELECT CASE Me.Move_Reason

    CASE "Order","Reason","Repair","ForParts","Disassembly"
        If Me.[+/-] = "+" Or  IsNull(Me.From_Location) Or  Me.From_Location = "" Or  IsNull(Me.Ref) Or  Not IsNull(Me.To_Location) Or  Me.To_Location <> "" Or  Me.Ref = "" Then
                MsgBox "ERROR! Please check, At least one field is incorrect"
                DoCmd.CancelEvent
        End If
 
END SELECT

Thanks so much, I had about 8 levels different "Move_Reason" with different criteria, could I add an ELSE and the another CASE and another IF and continue like that?

How you guys have this info immediately to hand is great, i cant even google the right question 99% of time to try and find solutions or info like this
 

Minty

AWF VIP
Local time
Today, 22:24
Joined
Jul 26, 2013
Messages
10,355
You can add I think up to 7 nested levels of the case statement, however it may be that your logic would be really simplified if you added some sort of flag to the various reasons and used that as a switch?

The case statement is very useful because it exits/ignores the other statements as soon as the criteria are met, and is much more readable that nested if's and elses.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:24
Joined
May 21, 2018
Messages
8,463
This can be shortened
Code:
If Me.[+/-] = "+" Or  IsNull(Me.From_Location) Or  Me.From_Location = "" Or  IsNull(Me.Ref) Or  Not IsNull(Me.To_Location) Or  Me.To_Location <> "" Or  Me.Ref = ""
To
Code:
If Me.[+/-] = "+" Or  (Me.From_Location & "") = "" Or (Me.Ref & "") = "" Or  (Me.To_Location & "") <> ""
Null & "" = ""
"" & "" = ""
 

Jordonjd

Member
Local time
Today, 23:24
Joined
Jun 18, 2020
Messages
96
This can be shortened
Code:
If Me.[+/-] = "+" Or  IsNull(Me.From_Location) Or  Me.From_Location = "" Or  IsNull(Me.Ref) Or  Not IsNull(Me.To_Location) Or  Me.To_Location <> "" Or  Me.Ref = ""
To
Code:
If Me.[+/-] = "+" Or  (Me.From_Location & "") = "" Or (Me.Ref & "") = "" Or  (Me.To_Location & "") <> ""
Null & "" = ""
"" & "" = ""

Thanks,

So (Me.Ref & "") is the same as IsNull(Me.Ref)

and Me.REF & "") = "" is the same as IsNull(Me.Ref) OR me.Ref = ""

?
 

Minty

AWF VIP
Local time
Today, 22:24
Joined
Jul 26, 2013
Messages
10,355
If you have an empty string "" and Concatenate a Null to it it is still an empty string ""

This is not the same as Adding (in the mathematical sense) a Null .
If you Add a null to anything you'll just get Null.

So

"" & Null = ""

"" + Null = Null

It's important to understand the subtle difference.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:24
Joined
May 21, 2018
Messages
8,463

Jordonjd

Member
Local time
Today, 23:24
Joined
Jun 18, 2020
Messages
96
ahhh ok, sorry my VBA knowledge is awful.

So the & is the concatenation

So (me.Ref & "") = ""

( [field] concatenate with "empty string") = " empty string"

If the field is null then it will = ""

If not then in then = me.ref value

And I guess my Not Isnull() is actually redundant because if it isnt null then [Field] = <> ""
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:24
Joined
May 21, 2018
Messages
8,463
And I guess my Not Isnull() is actually redundant because if it isnt null then [Field] = <> ""
The rest is correct, but the above is not correct. As written you need both the null check and empty string. These are two different things.

Field <> "" will return all fields with values but also null fields. Null and "" are not the same.

Nine times out of 10 when you see a blank field it is Null. However, in some cases it can be "" or even a space.
this lets you check for both null and "" at the same time
if (field & "") = ""
because if it is null it first converts it to a "". If it has a value then it just remains the value (string version)
There is a rare case where a field could have a space or spaces. Normally from importing some data. You can check all three

if trim(field & "") = "" then
this removes the spaces and converts them to ""
 

Jordonjd

Member
Local time
Today, 23:24
Joined
Jun 18, 2020
Messages
96
Right Chaps, I have done my best, and everything is technically working except the last:
If Me.[+/-] = "-" Then

By working I mean it seems to work and also then carry on the code from Line1 .. all except the last one, it seems to just stop at its end if ?


Code:
Select Case Me.Move_Reason

    Case "Order", "Repair", "ForParts", "Disassembly"
        If Me.[+/-] = "+" Or (Me.From_Location & "") = "" Or (Me.Ref & "") = "" Or (Me.To_Location & "") <> "" Then
       
        MsgBox "Error! Please check, At least one field is incorrect"
        DoCmd.CancelEvent
       
        Else
        GoTo Line1
       
        End If


    Case "Goods-In-Testing", "Movement"
        If (Me.From_Location & "") = "" Or (Me.To_Location & "") = "" Or (Me.Ref & "") <> "" Then
        MsgBox "Error! Please check, At least one field is incorrect"
        DoCmd.CancelEvent
       
        Else
        GoTo Line1
       
        End If
       

    Case "Return", "Finished-Goods", "FG-Rental", "Costed-Return"
        If Me.[+/-] = "-" Or (Me.From_Location & "") <> "" Or (Me.To_Location & "") = "" Or (Me.Ref & "") = "" Then
        MsgBox "Error! Please check, At least one field is incorrect"
        DoCmd.CancelEvent
       
        Else
        GoTo Line1
       
        End If
       

    Case "Lend-Issue", "Scrap"
        If Me.[+/-] = "+" Or (Me.From_Location & "") = "" Or (Me.To_Location & "") <> "" Then
        MsgBox "Error! Please check, At least one field is incorrect"
        DoCmd.CancelEvent
       
        Else
        GoTo Line1
       
        End If
       
       
    Case "Lend-Return", "SN-Capture"
        If Me.[=/-] = "-" Or (Me.To_Location & "") = "" Or (Me.From_Location & "") <> "" Then
        MsgBox "Error! Please check, At least one field is incorrect"
        DoCmd.CancelEvent
       
        Else
        GoTo Line1
       
        End If
   

    Case "Recount"
        If Me.[+/-] = "+" Then
            If (Me.From_Location & "") <> "" Or (Me.To_Location & "") = "" Then
            MsgBox "Error! Please check, At least one field is incorrect"
            DoCmd.CancelEvent
               
        Else
        GoTo Line1
       
        End If
       
        If Me.[+/-] = "-" Then
            If (Me.From_Location & "") = "" Or (Me.To_Location & "") <> "" Then
            MsgBox "Error! Please check, At least one field is incorrect"
            DoCmd.CancelEvent
            Else
            GoTo Line1
            End If
           
           
   

Line1:

If Me.txtparentloc = "Multiple" Then

MsgBox "Please make sure you have changed the MULTI location Qty's"

Else


I do have the End Select at the end of all the code, it seemed to be the only place it would go without popping up end select without etc etc errors
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:24
Joined
May 21, 2018
Messages
8,463
Not sure how this even compiles. You are missing an End if prior to "Line 1" and need an end select. Also you can get rid of the go to line 1 code. If it is not true then it will fall all the way through.

Code:
Select Case Me.Move_Reason

    Case "Order", "Repair", "ForParts", "Disassembly"
        If Me.[+/-] = "+" Or (Me.From_Location & "") = "" Or (Me.Ref & "") = "" Or (Me.To_Location & "") <> "" Then
            MsgBox "Error! Please check, At least one field is incorrect"
           DoCmd.CancelEvent
        End If


    Case "Goods-In-Testing", "Movement"
        If (Me.From_Location & "") = "" Or (Me.To_Location & "") = "" Or (Me.Ref & "") <> "" Then
           MsgBox "Error! Please check, At least one field is incorrect"
           DoCmd.CancelEvent
        End If
        

    Case "Return", "Finished-Goods", "FG-Rental", "Costed-Return"
        If Me.[+/-] = "-" Or (Me.From_Location & "") <> "" Or (Me.To_Location & "") = "" Or (Me.Ref & "") = "" Then
          MsgBox "Error! Please check, At least one field is incorrect"
          DoCmd.CancelEvent
        End If
        

    Case "Lend-Issue", "Scrap"
        If Me.[+/-] = "+" Or (Me.From_Location & "") = "" Or (Me.To_Location & "") <> "" Then
           MsgBox "Error! Please check, At least one field is incorrect"
           DoCmd.CancelEvent
        End If
        
        
    Case "Lend-Return", "SN-Capture"
        If Me.[=/-] = "-" Or (Me.To_Location & "") = "" Or (Me.From_Location & "") <> "" Then
           MsgBox "Error! Please check, At least one field is incorrect"
           DoCmd.CancelEvent
        End If
    

    Case "Recount"
        If Me.[+/-] = "+" Then
            If (Me.From_Location & "") <> "" Or (Me.To_Location & "") = "" Then
            MsgBox "Error! Please check, At least one field is incorrect"
            DoCmd.CancelEvent
         End If
        
        If Me.[+/-] = "-" Then
            If (Me.From_Location & "") = "" Or (Me.To_Location & "") <> "" Then
              MsgBox "Error! Please check, At least one field is incorrect"
              DoCmd.CancelEvent
            End If
        end if     
  end Select

If Me.txtparentloc = "Multiple" Then

MsgBox "Please make sure you have changed the MULTI location Qty's"

Else
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:24
Joined
May 21, 2018
Messages
8,463
A few variables would make this a lot easier to type
Code:
Dim PM as string  'plus minus
Dim FromLoc as string
Dim ToLoc as string
dim Ref as string

PM = PM & ""
FromLoc = Me.FromLoc_Location & ""
ToLoc = Me.ToLoc_Location & ""
Ref = me.Ref & ""

Select Case Me.Move_Reason

    Case "Order", "Repair", "ForParts", "Disassembly"
        If PM = "+" Or FromLoc = "" Or Ref = "" Or ToLoc <> "" Then
            MsgBox "Error! Please check, At least one field is incorrect"
           DoCmd.CancelEvent
        End If


    Case "Goods-In-Testing", "Movement"
        If FromLoc = "" Or ToLoc = "" Or Ref <> "" Then
           MsgBox "Error! Please check, At least one field is incorrect"
           DoCmd.CancelEvent
        End If
       

    Case "Return", "Finished-Goods", "FG-Rental", "Costed-Return"
        If PM = "-" Or FromLoc <> "" Or ToLoc = "" Or Ref = "" Then
          MsgBox "Error! Please check, At least one field is incorrect"
          DoCmd.CancelEvent
        End If
       

    Case "Lend-Issue", "Scrap"
        If PM = "+" Or FromLoc = "" Or ToLoc <> "" Then
           MsgBox "Error! Please check, At least one field is incorrect"
           DoCmd.CancelEvent
        End If
       
       
    Case "Lend-Return", "SN-Capture"
        If Me.[=/-] = "-" Or ToLoc = "" Or FromLoc <> "" Then
           MsgBox "Error! Please check, At least one field is incorrect"
           DoCmd.CancelEvent
        End If
   

    Case "Recount"
        If PM = "+" Then
            If FromLoc <> "" Or ToLoc = "" Then
            MsgBox "Error! Please check, At least one field is incorrect"
            DoCmd.CancelEvent
         End If
       
        If PM = "-" Then
            If FromLoc = "" Or ToLoc <> "" Then
              MsgBox "Error! Please check, At least one field is incorrect"
              DoCmd.CancelEvent
            End If
        end if    
  end Select

The last if check is the same as the one above so that looks like a logic error.
 

Jordonjd

Member
Local time
Today, 23:24
Joined
Jun 18, 2020
Messages
96
Not sure how this even compiles. You are missing an End if prior to "Line 1" and need an end select. Also you can get rid of the go to line 1 code. If it is not true then it will fall all the way through.

Code:
Select Case Me.Move_Reason

    Case "Order", "Repair", "ForParts", "Disassembly"
        If Me.[+/-] = "+" Or (Me.From_Location & "") = "" Or (Me.Ref & "") = "" Or (Me.To_Location & "") <> "" Then
            MsgBox "Error! Please check, At least one field is incorrect"
           DoCmd.CancelEvent
        End If


    Case "Goods-In-Testing", "Movement"
        If (Me.From_Location & "") = "" Or (Me.To_Location & "") = "" Or (Me.Ref & "") <> "" Then
           MsgBox "Error! Please check, At least one field is incorrect"
           DoCmd.CancelEvent
        End If
       

    Case "Return", "Finished-Goods", "FG-Rental", "Costed-Return"
        If Me.[+/-] = "-" Or (Me.From_Location & "") <> "" Or (Me.To_Location & "") = "" Or (Me.Ref & "") = "" Then
          MsgBox "Error! Please check, At least one field is incorrect"
          DoCmd.CancelEvent
        End If
       

    Case "Lend-Issue", "Scrap"
        If Me.[+/-] = "+" Or (Me.From_Location & "") = "" Or (Me.To_Location & "") <> "" Then
           MsgBox "Error! Please check, At least one field is incorrect"
           DoCmd.CancelEvent
        End If
       
       
    Case "Lend-Return", "SN-Capture"
        If Me.[=/-] = "-" Or (Me.To_Location & "") = "" Or (Me.From_Location & "") <> "" Then
           MsgBox "Error! Please check, At least one field is incorrect"
           DoCmd.CancelEvent
        End If
   

    Case "Recount"
        If Me.[+/-] = "+" Then
            If (Me.From_Location & "") <> "" Or (Me.To_Location & "") = "" Then
            MsgBox "Error! Please check, At least one field is incorrect"
            DoCmd.CancelEvent
         End If
       
        If Me.[+/-] = "-" Then
            If (Me.From_Location & "") = "" Or (Me.To_Location & "") <> "" Then
              MsgBox "Error! Please check, At least one field is incorrect"
              DoCmd.CancelEvent
            End If
        end if    
  end Select

If Me.txtparentloc = "Multiple" Then

MsgBox "Please make sure you have changed the MULTI location Qty's"

Else
I have made these changes and the it seems fine now. the only problem is the code continues as the error message as if it was ok but I think that is due to other statements I have further down which I guess aren't reliant on the results of this this statement..

I will have a look at the variables once I get all of this sorted, the 'plus minus = [+/-] is interesting

now I know this part is good, Im going to have a play around and see if I can clean everything else up, although I may be back to pester you guys.

Minty, Majp thanks a lot for your input and time
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:24
Joined
May 21, 2018
Messages
8,463
Sorry that code is wrong
PM = PM & ""
Should be
PM = Me.[+/-] & ""

Suprised anything worked.
 

Isaac

Lifelong Learner
Local time
Today, 15:24
Joined
Mar 14, 2017
Messages
8,738
So (Me.Ref & "") is the same as IsNull(Me.Ref)
No, but I think you were on the right track. The former is often used as a substitute (except also casting a wider net) (and then each having their respective comparison test) than the latter.
So they are twins in that sense :)
 

Jordonjd

Member
Local time
Today, 23:24
Joined
Jun 18, 2020
Messages
96
Sorry that code is wrong
PM = PM & ""
Should be
PM = Me.[+/-] & ""

Suprised anything worked.

haha I can only imagine what you guys must think sometimes when you see what and how people like me try to do things.
I am looking for some good starter course to get into, maybe udemy or something

I managed to get it working by adding a cancelevent at the very bottom of everything and using a Goto to bunny hop everything else if the error criteria is met.

Thanks again guys, im still going to look over everything else and try and tidy it up
 

Minty

AWF VIP
Local time
Today, 22:24
Joined
Jul 26, 2013
Messages
10,355
I would without fail change the field name of [-/+] .
That field name will cause you some problems further down the line I'm sure.

It breaks about 276 rules on field naming good practice - if you had added some spaces you would have had a full house! 😁
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:24
Joined
May 21, 2018
Messages
8,463
If I was doing a lot of this like you are I would make myself some helper functions that would save me a lot of time and make it much easier to read and to write.

To see if a field is blank (null, "" or spaces)
Code:
Public Function IsBlank(val As Variant) As Boolean
  'Check for Null, "", and a space/s
  IsBlank = (Trim(val & "") = "")
End Function

Then wrap it in another function for not blank
Public Function IsNotBlank(val As Variant) As Boolean
IsNotBlank = Not IsBlank(val)
End Function

To see if any field in a group is blank
Code:
Public Function IsAnyBlank(ParamArray Vals() As Variant) As Boolean
  Dim i As Integer
  For i = 0 To UBound(Vals)
    If IsBlank(Vals(i)) Then
      IsAnyBlank = True
      Exit Function
    End If
  Next i
End Function

To see if all are blank
Code:
Public Function AreAllBlank(ParamArray Vals() As Variant) As Boolean
  Dim i As Integer
  AreAllBlank = True
  For i = 0 To UBound(Vals)
    If Not IsBlank(Vals(i)) Then
      AreAllBlank = False
      Exit Function
    End If
  Next i
End Function

These can then be reused anywhere in your project. And the chance of making a mistake is minimized

You code becomes very readable
Code:
Select Case Me.Move_Reason

    Case "Order", "Repair", "ForParts", "Disassembly"
        If PM = "+" Or IsAnyBlank(FromLoc, Ref) Or IsNotBlank(ToLoc) Then
            MsgBox "Error! Please check, At least one field is incorrect"
           DoCmd.CancelEvent
        End If


    Case "Goods-In-Testing", "Movement"
        If IsAnyBlank(FromLoc, ToLoc) Or IsNotBlank(Ref) Then
           MsgBox "Error! Please check, At least one field is incorrect"
           DoCmd.CancelEvent
        End If
      

    Case "Return", "Finished-Goods", "FG-Rental", "Costed-Return"
        If PM = "-" Or IsNotBlank(FromLoc) Or IsAnyBlank(ToLoc, Ref) Then
          MsgBox "Error! Please check, At least one field is incorrect"
          DoCmd.CancelEvent
        End If
      

    Case "Lend-Issue", "Scrap"
        If PM = "+" Or IsBlank(FromLoc) Or IsNotBlank(ToLoc) Then
           MsgBox "Error! Please check, At least one field is incorrect"
           DoCmd.CancelEvent
        End If
      
      
    Case "Lend-Return", "SN-Capture"
        If Me.[=/-] = "-" Or IsBlank(ToLoc) Or IsNotBlank(FromLoc) Then
           MsgBox "Error! Please check, At least one field is incorrect"
           DoCmd.CancelEvent
        End If
  

    Case "Recount"
        If PM = "+" Then
            If IsNotBlank(FromLoc) Or IsBlank(ToLoc) Then
            MsgBox "Error! Please check, At least one field is incorrect"
            DoCmd.CancelEvent
         End If
      
        If PM = "-" Then
            If IsBlank(FromLoc) Or IsNotBlank(ToLoc) Then
              MsgBox "Error! Please check, At least one field is incorrect"
              DoCmd.CancelEvent
            End If
        End If
  End Select
End Sub
 

Users who are viewing this thread

Top Bottom