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
avv...@yahoo.com

[Allen Browne]

Try:

DatePart("q", [YourDateFieldNameHere])

--
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.

"Victoria" <Vict...@discussions.microsoft.com> wrote in message
news:A3D8BD09-1A99-4EFE...@microsoft.com...

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

"Victoria" <Vict...@discussions.microsoft.com> сообщил/сообщила в новостях
следующее: news:A3D8BD09-1A99-4EFE...@microsoft.com...

For this case Allen is absolutely right.
Bot for common case correct syntax is following:
Public Function getquarter(Mnth)

--
Alex

[Andy]

How about something like this:

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