Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Quarter (Access 2k)

    Good Afternoon,

    I have a table w/a DATEFIELD. In query QRY1 I use the LEFT function to strip down the DATEFIELD to retrieve ONLY the month number (i.e. Jan=1, Feb=2, etc.). Then I use QRY1 as the record source for a TOTALS query, QRY2. (I realize I could format the date field to retrieve the "Month"....however, field will show the number of the month but store the entire date...and I don't want the entire date....just the month number)

    I need to find a way to retrive the QUARTER of the year that the DATEFIELD represents (i.e. 12/2/06=4, 5/1/06=2, etc.). What must I do to accomplish this?

    Thanks in advance.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quarter (Access 2k)

    You could use something like this to calculate the quarter - where [field1] is the month number.

    Int(([field1]-1)/3)+1

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quarter (Access 2k)

    You can use the Month function to extract the month from a date. This is more dependable than using Left, since that would fail on a system with a different date format, such as dd/mm/yyyy or yyyy/mm/dd. Month([DateField]) will return 1, 2, ..., 12, regardless of the date format used.

    You can retrieve the quarter directly from the date field using
    <code>Format([DateField],"q")</code>
    or
    <code>(Month([DateField])-1)3+1</code>

    With your month field:
    <code>([MonthField]-1)3+1</code>

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Quarter (Access 2k)

    A simple way to do it is to use the quarter format on the date to return the quarter by using the format string "q":

    <code>Format([DATEFIELD], "q")</code>
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •