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 - 22 Nov 2006 14:20 GMT
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
> I'm still new to VBA and trying to write a simple function to derive gender
> from title, like this:
[quoted text clipped - 11 lines]
>
> Help?
Keith Wilby - 22 Nov 2006 14:50 GMT
<snip>
> btw, what will you do when the title is <snip> "Rev"?
Make a pot of tea? ;-)
Keith.
Rick Brandt - 22 Nov 2006 14:21 GMT
> I'm still new to VBA and trying to write a simple function to derive
> gender from title, like this:
[quoted text clipped - 11 lines]
>
> 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.

Signature
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Allen Browne - 22 Nov 2006 14:24 GMT
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

Signature
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
> I'm still new to VBA and trying to write a simple function to derive
> gender
[quoted text clipped - 13 lines]
>
> Help?
David W. Fenton - 22 Nov 2006 23:38 GMT
> 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?

Signature
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Tom Lake - 23 Nov 2006 03:47 GMT
> 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
Anthony - 22 Nov 2006 14:26 GMT
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
[quoted text clipped - 13 lines]
>
> Help?