Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / November 2006

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Agent Dagnamit - 22 Nov 2006 14:05 GMT
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?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.