assigning fiscal quarters

[chris]

Hello,

I am just starting to write queries for our DB, so if this
is a simple question, please forgive me.

I was wondering if anyone knew where to find an example of
a query (or is it a piece of code?) that assigns date
ranges to fiscal quarters. We are not using a traditional
calender year.

For example, I have a date field and need the following
date ranges to show up on the report (not the dates
themselves):

date field contents what should appear on report
(one date is stored,
not the ranges
themselves)
12/1/02 - 2/28/03 03 Winter Quarter
03/1/03 - 05/31/03 03 Spring Quarter
06/1/03 - 08/30/03 03 Summer Quarter
09/01/03 - 11/30/03 03 Fall Quarter

Any tips or hints are appreciated,
Christi

 

[Chris Large]

Hi

You'll need to create you own function, copy the following
code into a module:-

Public Function GetPeriod(dte As Date) As String
Dim str As String
str = Format(dte, "yy")
Select Case Month(dte)
Case 12, 1, 2
str = str & " Winter Quarter"
Case 3, 4, 5
str = str & " Spring Quarter"
Case 6, 7, 8
str = str & " Summer Quarter"
Case 9, 10, 11
str = str & " Fall Quarter"
End Select
GetPeriod = str
End Function


You can now use the GetPeriod function in a query say,
just as you would any standard Access function.

hth

Chris

 

[chris]

Chris,

Thank you very much for the code. I saved it as a module,
named it "GetPeriod" and tried to enter it in the QBE grid
as follows:

QtrName: GetPeriod([Date_field])

However, I get an error message 'Undefined
function "GetPeriod" in expression. Can tell what I have
entered incorrectly?

I appreciate any help as I'm new at generating queries.

Many Thanks,
Christi


QtrName: GetPeriod([Period])

 

[John Spencer (MVP)]

As a guess, you have named the module and the function both "GetPeriod". Try
naming the module "modGetPeriod" or the function "fGetPeriod". The function and
the module cannot have the same name.

Chris,

Thank you very much for the code. I saved it as a module,
named it "GetPeriod" and tried to enter it in the QBE grid
as follows:

QtrName: GetPeriod([Date_field])

However, I get an error message 'Undefined
function "GetPeriod" in expression. Can tell what I have
entered incorrectly?

I appreciate any help as I'm new at generating queries.

Many Thanks,
Christi

QtrName: GetPeriod([Period])

-----Original Message-----
Hi

You'll need to create you own function, copy the following
code into a module:-

Public Function GetPeriod(dte As Date) As String
Dim str As String
str = Format(dte, "yy")
Select Case Month(dte)
Case 12, 1, 2
str = str & " Winter Quarter"
Case 3, 4, 5
str = str & " Spring Quarter"
Case 6, 7, 8
str = str & " Summer Quarter"
Case 9, 10, 11
str = str & " Fall Quarter"
End Select
GetPeriod = str
End Function


You can now use the GetPeriod function in a query say,
just as you would any standard Access function.

hth

Chris


.