If....Elseif.....Else....Endif simple question, please help

[Agent Dagnamit]

I'm still new to VBA and trying to write a simple function to derive gender
from title, like this:

Public Sub test_ifthenelse()
Dim title, gend As String
title = "Mrs"
If title = "Mr" Then gend = "M"
ElseIf title = "Mrs" Then gend = "F"
End If

when this compliles, Access puts a colon ":" between the Else and If, and
when it runs, I get an "Else without if" error message - I'm tearing my hair
out and cant see what I'm going wrong.

Help?

 

[tina]

the only time you can put the action on the same line as the If...Then
section, is when the entire statement is one line, as

If something = True Then do this

and you're done.
when you have more than one action for a situation, or one or more Else
sections, you must use the multi-line syntax, as

If title = "Mr" Then
gend = "M"
Else If title = "Mrs" Then
gend = "F"
End If

you might want to consider using the Select Case statement, instead, as

Select Case title
Case "Mr"
gend = "M"
Case "Mrs", "Ms"
gend = "F"
Case Else
gend = "Unknown"
End Select

btw, what will you do when the title is "Dr"? "Prof"? "Rev"?

hth

 

[Rick Brandt]

I'm still new to VBA and trying to write a simple function to derive
gender from title, like this:

Public Sub test_ifthenelse()
Dim title, gend As String
title = "Mrs"
If title = "Mr" Then gend = "M"
ElseIf title = "Mrs" Then gend = "F"
End If

when this compliles, Access puts a colon ":" between the Else and If,
and when it runs, I get an "Else without if" error message - I'm
tearing my hair out and cant see what I'm going wrong.

Help?

There are two kinds of If-Then usages. One line and a block. You are mixing
those together.

One line looks like...

If title = "Mr" Then gend = "M"

....and there is NO Else or ElseIf

The block statement looks like...

If title = "Mr" Then
gend = "M"
ElseIf title = "Mrs" Then
gend = "F"
End If

Notice that in the block statement you have nothing after the "Then" on the same
line.

 

[Allen Browne]

There are 2 ways you can write an If statement.

Example1: Entire If ... Then ... on a single line:
If Amount <= 1 Then Result = "Small amount"

Example 2: Using an If ... Then *block* of code:
If Amount <= 1 Then
Result = "Small amount"
End If

The same applies if the Else is included, e.g.:
If Amount <= 1 Then Result = "Small amount" Else Result = "Not so small"
or as a If block:
If Amount <= 1 Then
Result = "Small amount"
Else
Result = "Not so small"
End If

In practice, I sugges you always use the block, never the single line. The
block is much more powerful (can include lots of lines), and a consistent
style is much easier to debug (i.e. there will always be a matching End If
line for every If statement.)

The colon in VBA does permit mulitple logical lines on one physical line.
For example, you could code:
A=4: B=3: C=A+B: Debug.Print C
all on the one line. Again, this is poor coding style, and harder to debug.

In your example, Access added the colon between the Else and If because the
previous If line was not a block If. Therefore a new line starting with
ElseIf made no sense to VBA, since a line starting with ElseIf only makes
sense inside a block If.

For your particular case, you might find it easier to use a Select Case
instead of an If block, since it more easily handles a wider range of
values:

Select Case Me.Title
Case "Mr", "Master", "Mr."
Me.gend = "M"
Case "Miss", "Ms", "Ms.", "Mrs", "Mrs."
Me.gend = "F"
End Select

 

[Anthony]

It should look like this, instead:

----------
Public Sub test_ifthenelse()

Dim title, gend As String

title = "Mrs"

If title = "Mr" Then

gend = "M"

ElseIf title = "Mrs" Then

gend = "F"

End If

End Sub
----------

Notice, I placed space between the lines, and also indented where necessary.
Code indentation is very important, because it's much easier to spot where a
block begins/ends, and especially when you begin nesting loops inside of
Select Case staments, nested in If statements that are nested in other loops
that are nested in If statements, that are also nested inside Select Case
Statements.

 

[Agent Dagnamit]

Thanks everyone, I didnt appreciate the importance of line spacing, the code
now works.

PS - yes, dealing with titles like Dr, Prof, etc isnt easy, but if I can
establish 90% of genders, I'll be happy enough

 

[Keith Wilby]

btw, what will you do when the title is <snip> "Rev"?

Make a pot of tea? ;-)

Keith.

 

[Klatuu]

I think there are a couple of better ways to do this. One choice would be to
use the Switch function:

strTitle = "Mr."
strTitle = Replace(strTitle, ".","") 'Remove periods for consistency

strGender = Switch(strTitle = "Mr", "M", strTitle = "Mrs", "F", strTitle =
"Miss", "F")

You said you could hit about 90%. I think that is optimistic. Dr, for
example is non gender specific, so will be a problem.

Another more dynamic solution would be to use a table with two fields, Title
and Gender. Then use the DLookup to determine the Gender. This way, when
you get one in that is not in your list, it is easy to add it to the table
rather than have to modify your code.

strGender = Nz(DLookup("[Genter]", "tblGenderTitles", "[Title] = '" &
Me.txtTitle & "'"),"U")

 

[Agent Dagnamit]

Thanks Klatuu, I've now learnt the "replace" function as well, which is useful.

I may use a lookup table, but for the moment I'm using this exercise to
write some if, else code.

BTW - in my line of work we dont have many Doctors as customers, so 90% is
achievable!

I think there are a couple of better ways to do this. One choice would be to
use the Switch function:

strTitle = "Mr."
strTitle = Replace(strTitle, ".","") 'Remove periods for consistency

strGender = Switch(strTitle = "Mr", "M", strTitle = "Mrs", "F", strTitle =
"Miss", "F")

You said you could hit about 90%. I think that is optimistic. Dr, for
example is non gender specific, so will be a problem.

Another more dynamic solution would be to use a table with two fields, Title
and Gender. Then use the DLookup to determine the Gender. This way, when
you get one in that is not in your list, it is easy to add it to the table
rather than have to modify your code.

strGender = Nz(DLookup("[Genter]", "tblGenderTitles", "[Title] = '" &
Me.txtTitle & "'"),"U")

Thanks everyone, I didnt appreciate the importance of line spacing, the code
now works.

PS - yes, dealing with titles like Dr, Prof, etc isnt easy, but if I can
establish 90% of genders, I'll be happy enough

 

[Mark]

One other thing. You need to declare the type for each variable. So Dim
title, gend As String becomes dim title as string, gend as string. In your
current code title is a variant. Not a big problem in your example, but
something I thought I'd pass on for your more resource hungry code.

Thanks Klatuu, I've now learnt the "replace" function as well, which is
useful.

I may use a lookup table, but for the moment I'm using this exercise to
write some if, else code.

BTW - in my line of work we dont have many Doctors as customers, so 90% is
achievable!

I think there are a couple of better ways to do this. One choice would
be to
use the Switch function:

strTitle = "Mr."
strTitle = Replace(strTitle, ".","") 'Remove periods for consistency

strGender = Switch(strTitle = "Mr", "M", strTitle = "Mrs", "F", strTitle
=
"Miss", "F")

You said you could hit about 90%. I think that is optimistic. Dr, for
example is non gender specific, so will be a problem.

Another more dynamic solution would be to use a table with two fields,
Title
and Gender. Then use the DLookup to determine the Gender. This way,
when
you get one in that is not in your list, it is easy to add it to the
table
rather than have to modify your code.

strGender = Nz(DLookup("[Genter]", "tblGenderTitles", "[Title] = '" &
Me.txtTitle & "'"),"U")

Thanks everyone, I didnt appreciate the importance of line spacing, the
code
now works.

PS - yes, dealing with titles like Dr, Prof, etc isnt easy, but if I
can
establish 90% of genders, I'll be happy enough

:

It should look like this, instead:

----------
Public Sub test_ifthenelse()

Dim title, gend As String

title = "Mrs"

If title = "Mr" Then

gend = "M"

ElseIf title = "Mrs" Then

gend = "F"

End If

End Sub
----------

Notice, I placed space between the lines, and also indented where
necessary.
Code indentation is very important, because it's much easier to spot
where a
block begins/ends, and especially when you begin nesting loops inside
of
Select Case staments, nested in If statements that are nested in
other loops
that are nested in If statements, that are also nested inside Select
Case
Statements.

message
I'm still new to VBA and trying to write a simple function to
derive
gender
from title, like this:

Public Sub test_ifthenelse()
Dim title, gend As String
title = "Mrs"
If title = "Mr" Then gend = "M"
ElseIf title = "Mrs" Then gend = "F"
End If

when this compliles, Access puts a colon ":" between the Else and
If, and
when it runs, I get an "Else without if" error message - I'm
tearing my
hair
out and cant see what I'm going wrong.

Help?

 

[Klatuu]

Not many doctors? Must be in the Funeral business

Thanks Klatuu, I've now learnt the "replace" function as well, which is useful.

I may use a lookup table, but for the moment I'm using this exercise to
write some if, else code.

BTW - in my line of work we dont have many Doctors as customers, so 90% is
achievable!

I think there are a couple of better ways to do this. One choice would be to
use the Switch function:

strTitle = "Mr."
strTitle = Replace(strTitle, ".","") 'Remove periods for consistency

strGender = Switch(strTitle = "Mr", "M", strTitle = "Mrs", "F", strTitle =
"Miss", "F")

You said you could hit about 90%. I think that is optimistic. Dr, for
example is non gender specific, so will be a problem.

Another more dynamic solution would be to use a table with two fields, Title
and Gender. Then use the DLookup to determine the Gender. This way, when
you get one in that is not in your list, it is easy to add it to the table
rather than have to modify your code.

strGender = Nz(DLookup("[Genter]", "tblGenderTitles", "[Title] = '" &
Me.txtTitle & "'"),"U")

Thanks everyone, I didnt appreciate the importance of line spacing, the code
now works.

PS - yes, dealing with titles like Dr, Prof, etc isnt easy, but if I can
establish 90% of genders, I'll be happy enough

:

It should look like this, instead:

----------
Public Sub test_ifthenelse()

Dim title, gend As String

title = "Mrs"

If title = "Mr" Then

gend = "M"

ElseIf title = "Mrs" Then

gend = "F"

End If

End Sub
----------

Notice, I placed space between the lines, and also indented where necessary.
Code indentation is very important, because it's much easier to spot where a
block begins/ends, and especially when you begin nesting loops inside of
Select Case staments, nested in If statements that are nested in other loops
that are nested in If statements, that are also nested inside Select Case
Statements.

I'm still new to VBA and trying to write a simple function to derive
gender
from title, like this:

Public Sub test_ifthenelse()
Dim title, gend As String
title = "Mrs"
If title = "Mr" Then gend = "M"
ElseIf title = "Mrs" Then gend = "F"
End If

when this compliles, Access puts a colon ":" between the Else and If, and
when it runs, I get an "Else without if" error message - I'm tearing my
hair
out and cant see what I'm going wrong.

Help?

 

[David W. Fenton]

In practice, I sugges you always use the block, never the single
line. The block is much more powerful (can include lots of lines),
and a consistent style is much easier to debug (i.e. there will
always be a matching End If line for every If statement.)

I dissent from this advice. I see no reason to use a full statement
when it is not necessary.

Does *anyone* have a compelling argument for avoiding the
single-line If statement?

 

[Tom Lake]

Does *anyone* have a compelling argument for avoiding the

single-line If statement?

Not I. I agree with you. I find a simple statement easier to read

If x > 6 Then Print x

as opposed to

If x > 6 Then
Print x
End If

Tom Lake