I need to evaluate a date against an annual date range to determine which quarter the date falls in within the annual date range.

We track policies and commissions. We need to know in which Policy Quarter a particular commission was received. Although the Policy Year is not realy a
fiscal year,  it does works much the same. I found a function (below) and tried it out with no success. Keep in mind that each policy has its own
Policy year with only about half being the same as a calendar year. anyway I have not had any luck using the function. Could someone tell me what I am doing wrong?

I got the function from this posting here
http://www.*-*-*.com/

In a query the following returns "#Error"
    Expr1: FiscalQuarter([AccountingMonth],[CommissionEffectiveDate])

Function FiscalQuarter(myDate As Date, Optional iFiscalYrStartMonth As Integer = 6) As Integer
'pass in the date you want to evaluate and
'the month that starts the fiscal year
    Dim iMonth As Integer
    iMonth = Val(Str(myDate))
    Select Case iMonth
    Case 1 To 3
        FiscalQuarter = 3
    Case 4 To 6
        FiscalQuarter = 4
    Case 7 To 9
        FiscalQuarter = 1
    Case 10 To 12
        FiscalQuarter = 2
    End Select
End Function

Thanks
Tony Vrolyk

See if this won't do what you want.

Steve

Public Function GetFiscalQtr(ByVal X As Date, dteFYE As Date)
   'X = date in question, dteFYE = Fiscal Year End
   Dim m As Integer
   Dim FMonthStart As Integer
   Dim FDayStart As Integer
   Dim FYearOffset As Integer

   FMonthStart = Month(dteFYE + 1)
   FDayStart = Day(dteFYE + 1)
   FYearOffset = Year(X) - Year(dteFYE)

   m = Month(X) - FMonthStart + 1
   If Day(X) < FDayStart Then m = m - 1
   If m < 1 Then m = m + 12

    Select Case m
    Case 1 To 3
        GetFiscalQtr = 1
    Case 4 To 6
        GetFiscalQtr = 2
    Case 7 To 9
        GetFiscalQtr = 3
    Case 10 To 12
        GetFiscalQtr = 4
    End Select

End Function

  I need to evaluate a date against an annual date range to determine which quarter the date falls in within the annual date range.

  We track policies and commissions. We need to know in which Policy Quarter a particular commission was received. Although the Policy Year is not realy a
  fiscal year,  it does works much the same. I found a function (below) and tried it out with no success. Keep in mind that each policy has its own
  Policy year with only about half being the same as a calendar year. anyway I have not had any luck using the function. Could someone tell me what I am doing wrong?

  I got the function from this posting here
  http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&safe=off&selm=eIya...

  In a query the following returns "#Error"
      Expr1: FiscalQuarter([AccountingMonth],[CommissionEffectiveDate])

  Function FiscalQuarter(myDate As Date, Optional iFiscalYrStartMonth As Integer = 6) As Integer
  'pass in the date you want to evaluate and
  'the month that starts the fiscal year
      Dim iMonth As Integer
      iMonth = Val(Str(myDate))
      Select Case iMonth
      Case 1 To 3
          FiscalQuarter = 3
      Case 4 To 6
          FiscalQuarter = 4
      Case 7 To 9
          FiscalQuarter = 1
      Case 10 To 12
          FiscalQuarter = 2
      End Select
  End Function

  Thanks
  Tony Vrolyk

As I was reading though your function I started getting the idea of what you were doing and then I modified it to the following. This works better for me since policy years are not defined as 365 days. For instance a policy may have an effective date of 07/01/02 and a renewal date of 07/01/03. If I use the renewal date as the dteFYE with your function then the first day of the 3rd, 6th and 9th month will return as the previous quarter. So I adjusted the function to work with the effective date (or the Fiscal Year Start).

I've tested it and it seems to be working. Let me know if you think I am missing anything and thanks for the help.

Tony

Public Function GetFiscalQtr(ByVal myDate As Date, dteFYS As Date)
   'myDate = date in question, dteFYS = Fiscal Year Start
   Dim m As Integer
   Dim FMonthStart As Integer
   Dim FMonthCurrent As Integer

   FMonthStart = Month(dteFYS)
   FMonthCurrent = Month(myDate)

   m = FMonthCurrent - FMonthStart
   If m < 1 Then m = m + 12

   Select Case m
    Case 0 To 2
        GetFiscalQtr = 1
    Case 3 To 5
        GetFiscalQtr = 2
    Case 6 To 8
        GetFiscalQtr = 3
    Case 9 To 11
        GetFiscalQtr = 4
    End Select

End Function
  See if this won't do what you want.

  Steve

  Public Function GetFiscalQtr(ByVal X As Date, dteFYE As Date)
     'X = date in question, dteFYE = Fiscal Year End
     Dim m As Integer
     Dim FMonthStart As Integer
     Dim FDayStart As Integer
     Dim FYearOffset As Integer

     FMonthStart = Month(dteFYE + 1)
     FDayStart = Day(dteFYE + 1)
     FYearOffset = Year(X) - Year(dteFYE)

     m = Month(X) - FMonthStart + 1
     If Day(X) < FDayStart Then m = m - 1
     If m < 1 Then m = m + 12

      Select Case m
      Case 1 To 3
          GetFiscalQtr = 1
      Case 4 To 6
          GetFiscalQtr = 2
      Case 7 To 9
          GetFiscalQtr = 3
      Case 10 To 12
          GetFiscalQtr = 4
      End Select

  End Function

    I need to evaluate a date against an annual date range to determine which quarter the date falls in within the annual date range.

    We track policies and commissions. We need to know in which Policy Quarter a particular commission was received. Although the Policy Year is not realy a
    fiscal year,  it does works much the same. I found a function (below) and tried it out with no success. Keep in mind that each policy has its own
    Policy year with only about half being the same as a calendar year. anyway I have not had any luck using the function. Could someone tell me what I am doing wrong?

    I got the function from this posting here
    http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&safe=off&selm=eIya...

    In a query the following returns "#Error"
        Expr1: FiscalQuarter([AccountingMonth],[CommissionEffectiveDate])

    Function FiscalQuarter(myDate As Date, Optional iFiscalYrStartMonth As Integer = 6) As Integer
    'pass in the date you want to evaluate and
    'the month that starts the fiscal year
        Dim iMonth As Integer
        iMonth = Val(Str(myDate))
        Select Case iMonth
        Case 1 To 3
            FiscalQuarter = 3
        Case 4 To 6
            FiscalQuarter = 4
        Case 7 To 9
            FiscalQuarter = 1
        Case 10 To 12
            FiscalQuarter = 2
        End Select
    End Function

    Thanks
    Tony Vrolyk

I just caught my own mistake
   If m < 1 Then m = m + 12
should be...
   If m < 0 Then m = m + 12

thanks again

  As I was reading though your function I started getting the idea of what you were doing and then I modified it to the following. This works better for me since policy years are not defined as 365 days. For instance a policy may have an effective date of 07/01/02 and a renewal date of 07/01/03. If I use the renewal date as the dteFYE with your function then the first day of the 3rd, 6th and 9th month will return as the previous quarter. So I adjusted the function to work with the effective date (or the Fiscal Year Start).

  I've tested it and it seems to be working. Let me know if you think I am missing anything and thanks for the help.

  Tony

  Public Function GetFiscalQtr(ByVal myDate As Date, dteFYS As Date)
     'myDate = date in question, dteFYS = Fiscal Year Start
     Dim m As Integer
     Dim FMonthStart As Integer
     Dim FMonthCurrent As Integer

     FMonthStart = Month(dteFYS)
     FMonthCurrent = Month(myDate)

     m = FMonthCurrent - FMonthStart
     If m < 1 Then m = m + 12

     Select Case m
      Case 0 To 2
          GetFiscalQtr = 1
      Case 3 To 5
          GetFiscalQtr = 2
      Case 6 To 8
          GetFiscalQtr = 3
      Case 9 To 11
          GetFiscalQtr = 4
      End Select

  End Function

    See if this won't do what you want.

    Steve

    Public Function GetFiscalQtr(ByVal X As Date, dteFYE As Date)
       'X = date in question, dteFYE = Fiscal Year End
       Dim m As Integer
       Dim FMonthStart As Integer
       Dim FDayStart As Integer
       Dim FYearOffset As Integer

       FMonthStart = Month(dteFYE + 1)
       FDayStart = Day(dteFYE + 1)
       FYearOffset = Year(X) - Year(dteFYE)

       m = Month(X) - FMonthStart + 1
       If Day(X) < FDayStart Then m = m - 1
       If m < 1 Then m = m + 12

        Select Case m
        Case 1 To 3
            GetFiscalQtr = 1
        Case 4 To 6
            GetFiscalQtr = 2
        Case 7 To 9
            GetFiscalQtr = 3
        Case 10 To 12
            GetFiscalQtr = 4
        End Select

    End Function

      I need to evaluate a date against an annual date range to determine which quarter the date falls in within the annual date range.

      We track policies and commissions. We need to know in which Policy Quarter a particular commission was received. Although the Policy Year is not realy a
      fiscal year,  it does works much the same. I found a function (below) and tried it out with no success. Keep in mind that each policy has its own
      Policy year with only about half being the same as a calendar year. anyway I have not had any luck using the function. Could someone tell me what I am doing wrong?

      I got the function from this posting here
      http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&safe=off&selm=eIya...

      In a query the following returns "#Error"
          Expr1: FiscalQuarter([AccountingMonth],[CommissionEffectiveDate])

      Function FiscalQuarter(myDate As Date, Optional iFiscalYrStartMonth As Integer = 6) As Integer
      'pass in the date you want to evaluate and
      'the month that starts the fiscal year
          Dim iMonth As Integer
          iMonth = Val(Str(myDate))
          Select Case iMonth
          Case 1 To 3
              FiscalQuarter = 3
          Case 4 To 6
              FiscalQuarter = 4
          Case 7 To 9
              FiscalQuarter = 1
          Case 10 To 12
              FiscalQuarter = 2
          End Select
      End Function

      Thanks
      Tony Vrolyk

--
Mike Sherrill
Information Management Systems

When you say "each policy has its own Policy year", what do you mean?
Say we have policy '111-222-333-444', and it starts on 01-Jun-2002.
Do its first four policy quarters look like this?

 PolicyNum        PolicyYear  Quarter        Start           End
 --
 111-222-333-444        2002        1  01-Jun-2002   31-Aug-2002
 111-222-333-444        2002        2  01-Sep-2002   30-Nov-2002
 111-222-333-444        2002        3  01-Dec-2002   28-Feb-2003
 111-222-333-444        2002        4  01-Mar-2003   31-May-2003

If not, what do they look like?

--
Mike Sherrill
Information Management Systems

Yes the quarters look like your example.

I don't see why my function is a problem. It takes the Effective date month
(as an integer) and subtracts it from the current date month (as an
integer). If the resulting value is under 0 than add 12. Then the Case
statement evaluates the number to return the correct quarter. I have tested
it several time and so far always returns the expected results.

If the Effective date is 02/01/2002 and the current date is 11/21/2002.
11-2 = 9
9 is over 0 so do nothing
9 resolves to quarter 3

If the Effective date is 11/01/2002 and the current date is 3/21/2003.
3-11 = -8
-8 + 12 = 4
4 resolves to quarter 2

Tony

[snip]

Try this.

 ? GetFiscalQtr("01-Feb-2000", "29-Nov-1999")
  2
 ? DateDiff("d", "01-Feb-2000", "29-Nov-1999")
 -64

My first quarter has more days than that.  YMMV.

--
Mike Sherrill
Information Management Systems

This is a perfect example of why I hate some posts that so-called experts
give to others questions. Rather than being helpful and giving reasons why
something doesn't work or examples of how to make it work, they give little
tidbits of useless crticism.

I frequently read posts where I understand what the poster but yet some
holier-then-thow god-of-programmers comes in and posts something useless
like "you'll need to explaing your db structure more before anyone can
answer you question." This frequenty happens to otherwise simple questions,
but because the poster didn't speak "Programmer" the responder felt it
necessary to basically berate the poster for not using the correct language.

To use a simple, non-programming example-
If some little old lady asks how she can install more memory and a new modem
in her CPU. Some "expert" will come back and say that she can't install
memory or a modem in her CPU because it is a small chip inside your computer
that  has it''s own buitl in cache memory and can not accept expansion
cards. Any idiot can tell you that when the little old lady said CPU, she
didn't mean her Central Pricessing Unit, she meant her whole computer. But
the "expert" can only speak "Computer Technician" and can't bring himself to
come down to everyone elses level and speak English.

As to the difference between "testing" and "running"  and "calculate" and
"know" - symantics. You obviously use the language of a seasoned programmer.
I am but a newcomer in these parts. If you were trying to be truly helpful
you should be able to red between the lines and respond appropriately rather
than posting in a way that implies I don't know what I am talking about.
Maybe I don't but you could say it in a much less confrontational style.

I still say that I am CALULATING the quarter. i am taking 2 dates and
subtracting the difference ... I'm sorry I had better be perfect in the way
I state this... I am calulating the difference between their respective
months and returning a number depending on what range the difference falls
in. In junior high math, subtacting was called a calculation. Maybe you
learned under New Math or Programmer Math. Sorry I missed those courses.
Even
you stated...

If you have some usefull info to contribute, maybe you should try formating
your response as if you are talking to people who don't understand the
language. After all, if the poster is an expert programmer and understands
the lingo they wouldn't be here asking questions in the first place.

Lastly, you don't understand my whole situation or how exactly I am going to
be using this function. I am not trying to develop a function for the
masses, just for my particular circumstances. In my db the current date will
never be before or more then 11 months after the Policy Effective date.

I could go on but won't. Beleive it or not I don't think I iterated my point
as well as I could have but it will have to do.

tony

You're forgiven.

Let me see if I can express this in a way you'll understand.

1. Quarters are facts.
2. Don't store facts in code.
2. Store facts in tables.
3. Don't write code to calculate quarters.
4. Build a table to store your quarters.
5. Store your quarters in a table.
6. When you need to know a specific quarter, don't write code to
calculate it.  Look in up in your table.

Clear now?

--
Mike Sherrill
Information Management Systems

Let me give you a few more details about my DB to make sure I understand
where you are suggesting storing the quarter.

tables:
Clients -> Policies -> CommissionReceipts

We have about 240 active clients with about 800 active policies. The
Policies table is where the Effective and Renewal dates are stored. These
constitute the Policy Year (similar to a fiscal year). Given that Effective
dates always fall on the 1st or 15th, that gives us 24 possible Policy
Years.

The CommissionReceipts table is where each commission received is recorded
along with its received date, accounting month, policy month and other info.
We want to know what quarter a commission receipt falls in. Typically each
policy will have 12 Receipts per year with occasional adjusting
transactions. If a policy renews it will have at least another 12 receipts
during the next year.

When renewing a policy will still be on the same 12 month cycle so any
month, even in a previous year will still resolve to the same quarter with
my function. If a policy were to change it's 'fiscal year' that would get
entered as a new separate policy.

Questions-
1. Are you suggesting storing the quarters in the policies table? Maybe
storing the first date of each quarter. Wouldn't that still require some
kind of calculation to determine in which quarter a Receipt falls?
2. Are you suggesting storing the quarter in the receipts table, for each
receipt? Again wouldn't thi still require a calculation when you write the
quarter to the CommissionReceipt record?
3. Are you suggesting storing quarters in another table altogether? If so,
what form would this take?

I don't see getting around comparing the CommissionReceipt date to something
else to determin the quarter. It seems to me the easiest to maybe store the
quarter number in the Receipts table when writing the record. But this still
requires some kind of calculation or comparison.

I think I understand your basic theory, but I am not sure how it applies
here given the number of policies and the variance in their Policy Years

Thanks
Tony

Look at the functional dependencies between the columns you have and
the columns you need to add to store data about quarters.  It's not
rocket science; treat data about quarters just like you do any other
data.  I posted a reasonable guess ages ago.

--
Mike Sherrill
Information Management Systems