If Else Statement in VBA

V

Victoria

Unfortunately, it's been 9 months since I've really worked in access and now
for some reason I can't get a simple statement to work! Help! What am I doing
wrong?

I took a date field, calculated month from it, which is a numerical value
and am now trying to translate it to quarter. What am I doing wrong?

Public Function getquarter(Mnth)

If Mnth = 1 Then getquarter = 1
ElseIf Mnth = 2 Then getquarter = 1
ElseIf Mnth = 3 Then getquarter = 1
ElseIf Mnth = 4 Then getquarter = 2
ElseIf Mnth = 5 Then getquarter = 2
ElseIf Mnth = 6 Then getquarter = 2
ElseIf Mnth = 7 Then getquarter = 3
ElseIf Mnth = 8 Then getquarter = 3
ElseIf Mnth = 9 Then getquarter = 3
ElseIf Mnth = 10 Then getquarter = 4
ElseIf Mnth = 11 Then getquarter = 4
ElseIf Mnth = 12 Then getquarter = 4
End If
End Function


Thanks!
-Victoria
(e-mail address removed)
 
J

Jim Burke in Novi

Allen has the easiest way to do it. Just to let you know, the problem with
your statement is that every single 'If, Then Else' needs and End If - you
cant' just close them all with one. And in your case, if you didn't have
Allen's solution, it would be easier to use a Select statement:

Select case mnth
Case 1 to 3
getquarter = 1
Case 4 to 6
getquarter = 2
Case 7 to 9
getquarter = 3
Case 10 to 12
getquarter = 4
End Select

Or you could get tricky and use:
getquarter = ((mnth-1) \ 3) + 1
 
J

John Spencer

You could write that as

Public Function getquarter(Mnth)

If IsNumeric(Mnth) = False then
'Note the first comparison must return
'results in another line
getQuarter = Null
ElseIf Mnth = 1 Then:getquarter = 1
ElseIf Mnth = 2 Then: getquarter = 1
ElseIf Mnth = 3 Then: getquarter = 1
ElseIf Mnth = 4 Then: getquarter = 2
ElseIf Mnth = 5 Then: getquarter = 2
ElseIf Mnth = 6 Then: getquarter = 2
ElseIf Mnth = 7 Then: getquarter = 3
ElseIf Mnth = 8 Then: getquarter = 3
ElseIf Mnth = 9 Then: getquarter = 3
ElseIf Mnth = 10 Then: getquarter = 4
ElseIf Mnth = 11 Then: getquarter = 4
ElseIf Mnth = 12 Then: getquarter = 4
Else: getquarter = Null
End If

End Function

As noted elsewhere there Are better methods to get the quarter.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
A

Alex

Victoria said:
Unfortunately, it's been 9 months since I've really worked in access and
now
for some reason I can't get a simple statement to work! Help! What am I
doing
wrong?

I took a date field, calculated month from it, which is a numerical value
and am now trying to translate it to quarter. What am I doing wrong?

Public Function getquarter(Mnth)

If Mnth = 1 Then getquarter = 1
ElseIf Mnth = 2 Then getquarter = 1
ElseIf Mnth = 3 Then getquarter = 1
ElseIf Mnth = 4 Then getquarter = 2
ElseIf Mnth = 5 Then getquarter = 2
ElseIf Mnth = 6 Then getquarter = 2
ElseIf Mnth = 7 Then getquarter = 3
ElseIf Mnth = 8 Then getquarter = 3
ElseIf Mnth = 9 Then getquarter = 3
ElseIf Mnth = 10 Then getquarter = 4
ElseIf Mnth = 11 Then getquarter = 4
ElseIf Mnth = 12 Then getquarter = 4
End If
End Function


Thanks!
-Victoria
(e-mail address removed)
For this case Allen is absolutely right.
Bot for common case correct syntax is following:
Public Function getquarter(Mnth)

If Mnth = 1 Then
getquarter = 1
ElseIf Mnth = 2 Then
getquarter = 1
ElseIf Mnth = 3 Then
getquarter = 1
ElseIf Mnth = 4 Then
getquarter = 2
ElseIf Mnth = 5 Then
getquarter = 2
ElseIf Mnth = 6 Then
getquarter = 2
ElseIf Mnth = 7 Then
getquarter = 3
ElseIf Mnth = 8 Then
getquarter = 3
ElseIf Mnth = 9 Then
getquarter = 3
ElseIf Mnth = 10 Then
getquarter = 4
ElseIf Mnth = 11 Then
getquarter = 4
ElseIf Mnth = 12 Then
getquarter = 4
End If
End Function
 
A

Andy

How about something like this:

Public Function getquarter(Mnth as integer) as Integer
getquarter = (Mnth-1)/3 + 1
end
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top