I am trying to construct a Function which will return a value based on
a date. The date is passed in the format DD/MM/YYYY and the output is
in the format Q12010 or Q32009 where Q1 or Q3 is the quarter and 2010
or 2009 is the year. The problem is that our year starts on the 1st
February, so Q1 is Feb, Mar and Apr, Q2 is May, Jun and July etc.

So if the date passed to the Function was 22/9/08 the function would
return Q32008

Any help/pointers greatly appreciated

BobbyDazzler wrote:
A calendar table that defines these periods would solve the problem nicely,
without the need for a performance-draining function.
http://sqlserver2000.databases.aspfa...dar-table.html
--
Bob Barrows

How about this (caution air code):

Public Function FYQ(dtInput As Date) As String
Select Case Month(dtInput)
Case 1
FYQ = "Q4" & (Year(dtInput)-1)
Case <= 4
FYQ = "Q1" & Year(dtInput)
Case <= 7
FYQ = "Q2" & Year(dtInput)
Case <= 10
FYQ = "Q3" & Year(dtInput)
Case Else
FYQ = "Q4" & Year(dtInput)
End Select
End Function

This assumes that Jan 2010 is within Q4 of your FY2009
Good Luck,
Fred




On Jun 18, 4:26*am, BobbyDazzler <david.a.mitch... (AT) inbox (DOT) com> wrote:

BobbyDazzler wrote:
This might get you started. Ex:
? QtrString(date)
Q22010

Public Function QtrString(datDate As Date)
Dim intQtr As Integer

Select Case Month(datDate)
Case 2 To 4
intQtr = 1
Case 5 To 7
intQtr = 2
Case 8 To 10
intQtr = 3
Case Else
intQtr = 4
End Select

QtrString = "Q" & intQtr & Year(datDate)

End Function

You can use this simple expression if TheDate is a date field.

Format(DateAdd("M",-1,[TheDate]),"\Qqyyyy")

BobbyDazzler wrote:
--

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