Results 1 to 6 of 6
  1. #1
    dinesh_ltjd is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    8

    Calculating Qurter out of Date

    I have a dataset in which i have a variable Bill date like this

    Bill Date
    1/16/2012
    11/16/2012
    11/16/2012
    11/16/2012
    11/16/2012
    1/16/2012
    1/16/2012
    1/16/2012
    1/16/2012
    1/16/2012
    1/16/2012


    Now I want to create another variable 'Quarter' and calculate this on the basis of Date and want the output as follow
    Quarter
    Q1-2012
    Q4-2012
    Q4-2012
    Q4-2012
    Q4-2012
    Q1-2012
    Q1-2012
    Q1-2012
    Q1-2012
    Q1-2012
    Q1-2012


    I was thinking of doing this with the combination of Datepart and if-elseif combinations. But as per my knowledge access donot support multiple Ifs statement and I dont want to write VBA code for the same

    Please suggest

    Thanks
    Dinesh

  2. #2
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Where are you performing this calculation?

    There's nothing wrong with nested IIF functions but they are probably not needed here.

    I assume your quarters are simply months 1-3, months 4-6, etc.

    OK, DatePart will extract the month. Dividing the month by 4 will give the quarter minus 1 as the integer part of the result. String concatenation gives you the format.

    "Q" & Int(DatePart("m", MyDate)) + 1 & "-" & DatePart("yyyy", MyDate)

  3. #3
    dinesh_ltjd is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    8
    Hi Rod,

    Thanks for your help

    But the given forluma is just adding up 1 to current month and concatinating it with the year

    11/29/2012 Q12-2012

    Thanks
    Dinesh

  4. #4
    dinesh_ltjd is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    8
    Hey Rod,

    I got it

    I just divided it by 4

    Quarter: "Q" & Int(DatePart("m",[Bill Date])/4)+1 & "-" & DatePart("yyyy",[Bill Date])

    Thanks again

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  6. #6
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Oops! Sorry about that; I forget to divide by 4 (well, typo really). ... and yes, Orange is correct, get the quarter directly with DatePart. I must learn to take a deep breath before launching into things.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 18
    Last Post: 02-06-2013, 12:43 PM
  2. Calculating Year to Date data in Form
    By barnes434 in forum Forms
    Replies: 3
    Last Post: 05-16-2012, 02:56 PM
  3. Calculating a date range
    By unicorn in forum Queries
    Replies: 6
    Last Post: 01-18-2012, 08:58 AM
  4. Calculating Expiry Date -
    By Jojojo in forum Programming
    Replies: 12
    Last Post: 10-05-2011, 12:05 PM
  5. Replies: 12
    Last Post: 02-22-2011, 03:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums