Another If statement question

[Agnostiker]

In a statement like

IF cond1 AND cond2 AND cond3 THEN ...

which condition is evaluated first by ACCESS?


Thanks.

Agnostiker

 

[Brianwarnock]

Does it matter?

Brian

 

[Agnostiker]

If performance doesn't matter, no.

Agnostiker

 

[Brianwarnock]

I can't think of a single reason for the compiler not to construct the code to evaluate the conditions in the order presented.

brian

 

[KenHigg]

I would put the simplest condition to evaluate first, etc.

kh

 

[Bat17]

I think that you will find that Access will evaluate them all anyway so it makes no odds.

Peter

 

[KenHigg]

Really. Hum...

Anyway to test that theory?

(I'm not disagreeing, just interested...)

kh

 

[KenHigg]

Well, Looks like you're right. I put a ref to txtbox that didn't exist in a second cond and even though the first cond failed it tried to eval the second cond anyway...

So I suppose the same would hold true for any compound conditions...

kh

 

[Agnostiker]

Here is a reply from an access newsgroup:

The most important thing is that *all* conditions are evaluated. Thus,
if Cond1 is False, still Cond2 and Cond3 will be evaluated. There is
not "shortuct boolean evaluation" in Access or VB/VBA. Otherwise,
expressions are being evaluated from left to right, IIRC.

If, say Cond2 depends on the result of Cond1, you need to take the If
statement apart:

If Cond1 Then
If Cond2 Then

...

HTH
Matthias Kläy
--
www.kcc.ch

I think he'll know...

 

[Pat Hartman]

think that you will find that Access will evaluate them all anyway so it makes no odds.

That this is true only for the IIF(). Case statements and nested If's are only evaluated as far as necessary.

I put a ref to txtbox that didn't exist in a second cond and even though the first cond failed it tried to eval the second cond anyway

This isn't a good test because it should cause a compile error. What you want to cause is a run time error. Try the code below:

Public Sub test1()
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
a = 0
b = 10
c = 20
If c = 20 Then
    d = c / b
Else
    d = c / a '  <-----no error
End If
If c <> 20 Then
    d = c / b
Else
    d = c / a  '  <-----error
End If

End Sub

But if you insert this line of code into the procedure:

d = IIf(c = 20, c / b, c / a)

- it always errors because all expressions are evaluated.

 

[ChrisO]

As the original question was asked all AND’ed conditions are evaluated even if the first result is False.

It’s a bit of a read but there were some timing tests done on this subject here.

Hope that helps.

Regards,
Chris.

 

[Agnostiker]

Dear members,

thanks for all your oppinions.

What do you think about this little test?

Function IfTest()
    Dim A%, B%
    A = 5: B = 20
    If A = B And vbNo = MsgBox("Hello") Then
        MsgBox ("That you will nerver see")
    End If
End Function

You can exchange the positions of the two conditions or try it with three of them - it's always the same result: Hello!
Do you think this will decide our little question?

Greetings from Berlin

Agnostiker

 

[KenHigg]

I don't see how:

If c = 20 Then
d = c / b
Else
d = c / a ' <-----no error
End If
If c <> 20 Then
d = c / b
Else
d = c / a ' <-----error
End If

Applies to the question...?

kh

 

[Pat Hartman]

To answer the original question, the conditions are evaluated in order. Based on the example below it appears that all parts of a single contdition are evaluated. So in the first test which is an OR, the second part of the condition seems to be evaluated even when the first part is true. I expected this to be true for AND relations but not OR's. Here is a situation where mainframe compilers work differently.
In any case, only the true path of a condition continues to be evaluated so the divide by zero expressions only throw an error if the IF evaluates to their path.

Public Sub test1()
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
a = 0
b = 10
c = 20
If c = 20 Or b / a = 0 Then    ' divide by zero error
End If
If c = 20 And b / a = 0 Then    ' divide by zero error
End If
If c = 20 And b = 10 Then
    d = c / b
Else
    d = c / a  ' no error for divide by zero
End If
' d = IIf(c = 20, c / b, c / a)
If c <> 20 And b <> 10 Then
    d = c / b
Else
    d = c / a  ' divide by zero  error
End If
End Sub

 

[ChrisO]

Option Explicit
Option Compare Text

' What do you think about this little test?

Function IfTest()
    Dim A%, B%
    
    A = 5: B = 20
    
    If A Then
        MsgBox "1 True"    [color=green]' < 1 True[/color]
    Else
        MsgBox "1 False"
    End If
    
    If B Then
        MsgBox "2 True"    [color=green]' < 2 True[/color]
    Else
        MsgBox "2 False"
    End If
    
    If A = True Then
        MsgBox "3 True"
    Else
        MsgBox "3 False"   [color=green]' < 3 False[/color]
    End If
    
    If B = True Then
        MsgBox "4 True"
    Else
        MsgBox "4 False"   [color=green]' < 4 False[/color]
    End If
    
    If A And B Then
        MsgBox "5 True"    [color=green]' < 5 True[/color]
    Else
        MsgBox "5 False"
    End If
    
    If A And (B = True) Then
        MsgBox "6 True"
    Else
        MsgBox "6 False"   [color=green]' < 6 False[/color]
    End If
    
    If (A And B) = True Then
        MsgBox "7 True"
    Else
        MsgBox "7 False"   [color=green]' < 7 False[/color]
    End If
    
    If Not A And B = True Then
        MsgBox "8 True"
    Else
        MsgBox "8 False"   [color=green]' < 8 False[/color]
    End If
    
    If Not A And B = False Then
        MsgBox "9 True"
    Else
        MsgBox "9 False"   [color=green]' < 9 False[/color]
    End If
    
    If Not (A And B = True) Then
        MsgBox "10 True"   [color=green]' < 10 True[/color]
    Else
        MsgBox "10 False"
    End If
   
End Function

' That's what I think about this little test?

Regards,
Chris.

 

[ChrisO]

I’m not disagreeing Pat, simply posting to someone else and out of time frame.

Regards,
Chris.

 

[KenHigg]

Acid test:

If f_firstTest(3) = 30 And f_secondTest(7) = 100 Then
MsgBox "Hello"
End If

In debug the tracer stops on the if line, then jumps to the first function and steps through it, then even if the first condition fails the tracer jumps to the second function, steps through it, and then back to the end if (Skipping the msgbox if either cond's fail).

Conclusion: In the past I would order cond's thinking that if the first one failed then I wouldn't have to take a performance hit evaluating the second cond, etc. While I now see that this is not the case, I may be able to nest 'if's if I need to avoid extraneous processing...

Right?

kh

 

[Agnostiker]

Hi Chris,

sorry, but I don't understand your answer - maybe it's due to my modest english skills

In your first post you wrote:

As the original question was asked all AND’ed conditions are evaluated even if the first result is False.

And this is what I wanted to demonstrate with my 'little test'! (And what I didn't know before) Is it wrong?
Maybe the 'Acid test' from KenHigg ist more instructive (Thank you for this, Ken! And I think, your conclusions are well done.)

Pat wrote:

...the second part of the condition seems to be evaluated even when the first part is true. I expected this to be true for AND relations but not OR's. Here is a situation where mainframe compilers work differently. ...

This was the reason for my original question, known from another compiler.

So far as I can understand your Code, Chris, it will demonstrate, that the constant 'TRUE' is defined as '-1'. I'am wrong?

So please help me to understand your reply.

With friendly regards

Agnostiker

 

[ChrisO]

G'day Agnostiker.

There is nothing wrong with 'your little test' except it is not so little.

Who could predict the outcome of tests 9 and 10 in my last post, not me that's for sure.

But now for the serious side of things, from one of Pat's posts...

   d = IIf(c = 20, c / b, c / a)

If either a or b ever become zero an error will occur in VBA.
The real problem is that they both may remain non-zero for some time and then fail.
The next problem is that it can't be protected against in the IIf function.
It's almost enough to suggest... don't do divisions in IIf functions in VBA.

The next part of your question was about the value of True and you are correct it is -1 in VBA.

But first let's go back to the AND logic and to keep it simple we will just use numbers.
    
    If 7 Then
        MsgBox "11 True"   ' < 11 True
    Else
        MsgBox "11 False"
    End If
   
    If 8 Then
        MsgBox "12 True"   ' < 12 True
    Else
        MsgBox "12 False"
    End If
   
    If 7 And 8 Then
        MsgBox "13 True"
    Else
        MsgBox "13 False"  ' < 13 False
    End If
    
A question might be raised as to why, if we AND two Trues, the result is False.
    
    If 7 And 9 Then
        MsgBox "14 True"   ' < 14 True
    Else
        MsgBox "14 False"
    End If
    
The answer is that it is not always False...sometimes it is True.
It's not a problem directly with the numbers but the bit positions the numbers create.


   In 16 bit binary integers...

   0000 0000 0000 0111  = 7      7 is not 0 and therefore it is True
   0000 0000 0000 1000  = 8      8 is not 0 and therefore it is True

The AND logic test is performed vertically bit by bit and it uses the following truth table.
   In this case 0 = False and True = 1

   0 AND 0 = 0
   0 AND 1 = 0
   1 AND 0 = 0
   1 AND 1 = 1

   or we could say

   False AND False = False
   False AND True  = False
   True  AND False = False
   True  AND True  = True

   Let's now apply that truth table to 7 AND 8

   0000 0000 0000 0111  = 7      7 is not 0 and therefore it is True
   0000 0000 0000 1000  = 8      8 is not 0 and therefore it is True
   AND'ed bitwise vertically
   0000 0000 0000 0000  = 0      0 is False.


   Let's now apply that truth table to 7 AND 9

   0000 0000 0000 0111  = 7      7 is not 0 and therefore it is True
   0000 0000 0000 1001  = 9      9 is not 0 and therefore it is True
   AND'ed bitwise vertically
   0000 0000 0000 0001  = 1      1 is not 0 and therefore it is True

Note that I say "1 is not 0 and therefore it is True."
That is not the same as saying "1 is not 0 and therefore it is equal to True."

The VBA Constant True = -1     and 1 <> -1

In this case 1 is True but it is not equal to True.

Hope that helps and doesn't muddy the waters.


Regards,
Chris.

 

[Agnostiker]

Hallo Chris,

thank you for your very interesting and instructive explanation!
I like this!
(I've also read this "Until-EOF-Test". Beautiful!)

No, you really cleared the water and now, I think, I know what you were aiming to.

Here's another one, and I hope it will amuse a little bit (if you try to get the answer before running it ):

    ' Condition 1
    If 1 Or 2 = True Then
        MsgBox "1 True"
    Else
        MsgBox "1 False"
    End If
    
    'Condition 2
    If (1 Or 2) = True Then
        MsgBox "2 True"
    Else
        MsgBox "2 False"
    End If
    
    'Condition 3
    If 1 Or -2 = True Then
        MsgBox "3 True"
    Else
        MsgBox "3 False"
    End If
    
    'Condition 4
    If (1 Or -2) = True Then
        MsgBox "4 True"
    Else
        MsgBox "4 False"
    End If

With friendly regards

Willi
(Agnostiker)