If Else Statement in VBA

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

 

[Allen Browne]

Try:

DatePart("q", [YourDateFieldNameHere])

 

[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

 

[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

 

[Alex]

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

 

[Andy]

How about something like this:

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