Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200

    Unanswered: Access Reporting

    Hi,

    I have a small report that prints all Outstanding Bill for the last 1,2,3,4,and greater than 4 months.
    But when i print according the months but I cannot group all the months greater than 4 as a single group.

    Now I can print as:


    Outstanding Bills of Current Month:
    Date Bill number
    Outstanding Bills of Last 1 Month:
    Date Bill number
    Outstanding Bills of Last 2 Month:
    Date Bill number
    Outstanding Bills of Last 3 Month:
    Date Bill number
    Outstanding Bills Last 4 Month:
    Date Bill number
    Outstanding Bills of Last 5 Month:
    Date Bill number
    Outstanding Bills Last 6Month:
    Date Bill number ......

    I want to print it as:

    Outstanding Bills of Current Month:
    Date Bill number
    Outstanding Bills of Last 1 Month:
    Date Bill number
    Outstanding Bills of Last 2 Month:
    Date Bill number
    Outstanding Bills of Last 3 Month:
    Date Bill number
    Outstanding Bills Greater than 3 Months
    Date Bill number

    Is there a way i can read all months greater than 4 as one particular value.

    Thanks Michael

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    It can be done ... How is the data being provided to the report? Does the report run off of a query? Does it sit on the table in question?

  3. #3
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200
    Hi,

    Thanks for the responce.
    Yes the report i generated by a Query.
    The query is take from a table which has a field date,which enters the date we register.The query has a field called Month which is DateDiff("m",Date,Now()) which gets the number of months since it has been registered.Eg: a deal dated in the month of October will return the value 2 for this month field in the Query.

    We use this Query to build the report.
    What i want is to display all deals for the 1st month,2nd Month,3rd Month and All months Greater than 3.How to group all months greater than 3 as a single one .

    Thanks for ur help
    Michael

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by mickykt
    Month and All months Greater than 3.How to group all months greater than 3 as a single one .

    Thanks for ur help
    Michael
    You could assign a key based on the value of your DateDiff function. Perhaps something along the lines of:

    CASE DateDiff("m", Date, Now()) WHEN 1 THEN 1 WHEN 2 THEN 2 ELSE 3 END AS yourGoupingLevel

    That will assign either 1, 2 or 3 to each record based on how outstanding they are. If it's more then 2 months, then it will be set to 3 regardless of the actual number of months outstanding. Then you could use that value for grouping at the report level.

  5. #5
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200
    HI

    can we use this case in a select Query while building the Query itself
    If possible can u please show me the syntax

    select case DateDiff("m",Date,Now())
    when DateDiff("m",Date,Now())=1 then ....

    so on,I get a syntax error,

    Thanks
    Michael

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by mickykt
    HI

    can we use this case in a select Query while building the Query itself
    If possible can u please show me the syntax

    select case DateDiff("m",Date,Now())
    when DateDiff("m",Date,Now())=1 then ....

    so on,I get a syntax error,

    Thanks
    Michael
    My apologies. The case statement is specific to t-sql. I'm used to using adp projects and hooking into sql server. For access only, an iif (or nested iif depending on how crazy you want to get) would be best:

    select iif(datediff("m", Date, now()) > 2, 3, datediff("m", Date, Now())

Posting Permissions

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