help on nested if statements

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Abbey Normal said:
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
Click to expand...

This line of code
If (IsNull(.Fields("Metal"))) Then .Fields("CatalogRef") =
.Fields("Plastic") & " / " & .Fields("Lens")
Click to expand...

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 -----
 
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?
Dirk Goldgar said:
Abbey Normal said:
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
Click to expand...

This line of code
If (IsNull(.Fields("Metal"))) Then .Fields("CatalogRef") =
.Fields("Plastic") & " / " & .Fields("Lens")
Click to expand...

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 -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Abbey Normal said:
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?
Click to expand...

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.
 
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,

Dirk Goldgar said:
Abbey Normal said:
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?
Click to expand...

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Abbey Normal said:
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,
Click to expand...

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.
 
Got it! Thanks,,,

Dirk Goldgar said:
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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top