help on nested if statements

[Abbey Normal]

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

 

[Dirk Goldgar]

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

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

 

[Abbey Normal]

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

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

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

(please reply to the newsgroup)

 

[Dirk Goldgar]

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?

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.

 

[Abbey Normal]

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

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)

 

[Dirk Goldgar]

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,

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.

 

[Abbey Normal]

Got it! Thanks,,,

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)