help on nested if statements

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Abbey Normal - 03 May 2005 00:41 GMT

Help! I'm going around in circles in this one. Its apparent I'm missing
something about how VBA handles if/then/else statements. Debugging it shows
that always performing the "otherwise" code even if it already populated it
with the plastic field. WHICH I WOULD EXPECT since there is no ELSE statement
there, but if I put it in (right before the "otherwise" code, then its
pertaining to the first IF? [because then if CatalogRef isNot blank, it
populates it anyway] I don't understand why another END IF isn't required and
the one I have there - I have two IF's. Also, I have tried Else IF and Else -
and it didnt like that either: It either flat out wouldnt let me enter it or
I got the wrong results. Can anyone give me some pointers on how to correctly
write this? THANKS....

'If CatalogRef already has a value, dont bother with this
If (IsNull(.Fields("CatalogRef"))) Then

'If the metals field is blank, we'll use plastic
If (IsNull(.Fields("Metal"))) Then .Fields("CatalogRef") =
.Fields("Plastic") & " / " & .Fields("Lens")

'otherwise use the metal
.Fields("CatalogRef") = .Fields("Metal") & " / " & .Fields("Lens")
End If

Reply to this Message

Dirk Goldgar - 03 May 2005 03:13 GMT

> Help! I'm going around in circles in this one. Its apparent I'm
> missing something about how VBA handles if/then/else statements.
[quoted text clipped - 19 lines]
> .Fields("CatalogRef") = .Fields("Metal") & " / " &
> .Fields("Lens") End If

This line of code

> If (IsNull(.Fields("Metal"))) Then .Fields("CatalogRef") =
> .Fields("Plastic") & " / " & .Fields("Lens")

is a single-line If, not a block If, and so does not take an End If
statement. I think what you meant to write was this:

'----- start of revised code -----
'If CatalogRef already has a value, dont bother with this
If (IsNull(.Fields("CatalogRef"))) Then

'If the metals field is blank, we'll use plastic
If (IsNull(.Fields("Metal"))) Then
.Fields("CatalogRef") = _
Fields("Plastic") & " / " & .Fields("Lens")
Else
'otherwise use the metal
.Fields("CatalogRef") = _
.Fields("Metal") & " / " & .Fields("Lens")
End If
End If
'----- end of revised code -----

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Abbey Normal - 03 May 2005 04:14 GMT

Thanks, your code worked perfectly. When i added the else and end if's I got
the error saying there was no block if. So does it make a difference what
line it shows up on? that was the only difference I could see i.e.:
If xxxx then
[code here]
else
[more code here]
End If
and that's what make it a "block" if?

> > Help! I'm going around in circles in this one. Its apparent I'm
> > missing something about how VBA handles if/then/else statements.
[quoted text clipped - 43 lines]
> End If
> '----- end of revised code -----

Reply to this Message

Dirk Goldgar - 03 May 2005 04:25 GMT

> Thanks, your code worked perfectly. When i added the else and end
> if's I got the error saying there was no block if. So does it make a
[quoted text clipped - 6 lines]
> End If
> and that's what make it a "block" if?

Right. If you look up "If...Then...Else Statement" in the VB online
help, you'll see that it has a single-line format and a block format.
The block format, which puts the statement(s) to be executed on a
separate line from the condition being tested, is the only one that
takes the End If statement, because that statement is needed to tell the
compiler where the block ends.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Abbey Normal - 03 May 2005 15:55 GMT

Do you have a specific URL for that On-line help? I don't see anything on
block if when I search within Access itself. I also don't see a Visual Basic
segment in the Micrsoft Office Online discussion groups. Thanks,

> > Thanks, your code worked perfectly. When i added the else and end
> > if's I got the error saying there was no block if. So does it make a
[quoted text clipped - 13 lines]
> takes the End If statement, because that statement is needed to tell the
> compiler where the block ends.

Reply to this Message

Dirk Goldgar - 03 May 2005 20:58 GMT

> Do you have a specific URL for that On-line help? I don't see
> anything on block if when I search within Access itself. I also don't
[quoted text clipped - 7 lines]
>> takes the End If statement, because that statement is needed to tell
>> the compiler where the block ends.

By "online help", I was actually referring to the built-in help files --
as opposed to printed materials -- and not to an Internet resource.
Sorry for confusing you; my usage dates back to the pre-web days.

If you open any code module or press Alt+F11 to open the VB editor, and
then enter "If...Then...Else Statement" in the help search box, you'll
find the topic I was referring to.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Abbey Normal - 04 May 2005 05:09 GMT

Got it! Thanks,,,

> > Do you have a specific URL for that On-line help? I don't see
> > anything on block if when I search within Access itself. I also don't
[quoted text clipped - 15 lines]
> then enter "If...Then...Else Statement" in the help search box, you'll
> find the topic I was referring to.

Reply to this Message