Multiple "And"s in an If Then Statement

[Reese]

[Solved] Multiple "And"s in an If Then Statement

I have the following code on an After Update event:

If Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" Then
Me.ProgPriceTxt.Value = "85"
End If
​

This works fine. When I add another "And" to the statement, however, it no longer functions:

If Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" And Me.PavRentCheck = False Then
Me.ProgPriceTxt.Value = "85"
End If
​

Is it possible to put three conditions into an And statement? This thread seems to imply so (it's a different situation, but it seems close enough).

I believe it doesn't have anything to do with my text boxes or fields because this same issue has occurred in other places when I tried to have three conditions in an And statement. So far I've just said "screw it" and moved on, but it would be nice to address this. Thanks.

 

[Reese]

I may have found a solution, but I'd like confirmation that this is the best way. I nested the above If statement into another If statement:

If Me.PavRentCheck.Value = False Then

If Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" Then
Me.ProgPriceTxt.Value = "85"
End If
​

ElseIf Me.PavRentCheck.Value = True Then

If Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" Then
Me.ProgPriceTxt.Value = "75"
End If
​

End If

Thanks.

 

[TJPoorman]

I notice in your first statement you only specify what should happen if the value is false, but not true. Have you tried the following:

If Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" And Me.PavRentCheck = False Then
    Me.ProgPriceTxt.Value = "85"
ElseIf Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" And Me.PavRentCheck = True Then
    Me.ProgPriceTxt.Value = "75"
End If

 

[Reese]

That's actually the exact phrasing that I was using, I was just posting one part of it to make the post shorter. In hindsight perhaps I should have included it in the post anyway.

Thank you for the suggestion, though.

 

[TJPoorman]

There is no limit to how many And's you can place in an If statement (There are, but I very very highly doubt you will ever be near the number). The thing with And is that every statement has to match for it to complete.

I would recommend placing a code break on the first line of the If statement, then hover over each of the variable to verify their value. This should narrow down where your And's are breaking.

 

[MSAccessRookie]

I have the following code on an After Update event:

If Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" Then
Me.ProgPriceTxt.Value = "85"
End If
​

This works fine. When I add another "And" to the statement, however, it no longer functions:

If Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" And Me.PavRentCheck = False Then
Me.ProgPriceTxt.Value = "85"
End If
​

Is it possible to put three conditions into an And statement? This thread seems to imply so (it's a different situation, but it seems close enough).

I believe it doesn't have anything to do with my text boxes or fields because this same issue has occurred in other places when I tried to have three conditions in an And statement. So far I've just said "screw it" and moved on, but it would be nice to address this. Thanks.

Consider giving your If() Statement more directions as to how to process the conditions by adding "(" and ")" as required. On possible suggestion might look like the following:

    If [B]([COLOR=green](Me.Program_Type.Value = "(1) 45 Minute Formal")[/COLOR] And _[/B]
[B]       [COLOR=royalblue](Me.Cost_Category = "Full Price")[/COLOR] And _[/B]
[B]       [COLOR=darkorchid](Me.PavRentCheck = False)[/COLOR])[/B] Then

-- Rookie

 

[Reese]

Aha...

So the problem was a separate issue. Apparently VBA only recognizes something as "False" if it contains "0", not "0" or is null. I was going under the assumption that it recognized both as a state of being "False".

After setting PayRentCheck to a default value of "0" in the table, having 3 And statements works without a problem. Personally I think it's silly that "is null" isn't recognized but I've discovered that Access isn't very intuitive.

Thanks everyone.

 

[MSAccessRookie]

Aha...

So the problem was a separate issue. Apparently VBA only recognizes something as "False" if it contains "0", not "0" or is null. I was going under the assumption that it recognized both as a state of being "False".

After setting PayRentCheck to a default value of "0" in the table, having 3 And statements works without a problem. Personally I think it's silly that "is null" isn't recognized but I've discovered that Access isn't very intuitive.

Thanks everyone.

If that is the case, then I believe that the Nz() Function could work if you set the conversion value to 0.

-- Rookie

 

[Brianwarnock]

Aha...

So the problem was a separate issue. Apparently VBA only recognizes something as "False" if it contains "0", not "0" or is null. I was going under the assumption that it recognized both as a state of being "False".

After setting PayRentCheck to a default value of "0" in the table, having 3 And statements works without a problem. Personally I think it's silly that "is null" isn't recognized but I've discovered that Access isn't very intuitive.

Thanks everyone.

As far as Access is concerned 0 is false, anything else is true.

Brian

 

[Brianwarnock]

Thank you Pat, that'll teach me to short cut my answer. I was trying to say that if you test for FALSE Access will treat everything else but 0 as TRUE.
I presume that if you test f or TRUE then 0 and NULL will be treated as FALSE, in which case the poster could have tested for <> TRUE.
, or have I just dug a deeper hole?

Brian

 

[Reese]

Ok... you guys have gone onto a whole new level of discussion at this point; I'm lost with some of what you guys are talking about. I'm happy with my solution, though, so it's okay.