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 avvigil@yahoo.com
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" wrote in message news:A3D8BD09-1A99-4EFE-B33B-396534A17EEC@microsoft.com... > 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 > avvigil@yahoo.com
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 "Victoria" wrote: > 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 > avvigil@yahoo.com
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 Victoria wrote: > 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 > avvigil@yahoo.com
"Victoria" ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ: news:A3D8BD09-1A99-4EFE-B33B-396534A17EEC@microsoft.com... > 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 > avvigil@yahoo.com 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 -- Alex
How about something like this: Public Function getquarter(Mnth as integer) as Integer getquarter = (Mnth-1)/3 + 1 end Alex wrote: > "Victoria" ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ > ÓÌÅÄÕÀÝÅÅ: news:A3D8BD09-1A99-4EFE-B33B-396534A17EEC@microsoft.com... > >> 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 >> avvigil@yahoo.com >> > 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 > > >
