Results 1 to 9 of 9

Thread: Report

  1. #1
    Join Date
    Sep 2011
    Posts
    5

    Question Unanswered: Report

    Hi, I'm not sure if this is the correct thread to post this but here we go.

    So I have a simple database set up for customer service contracts. These contracts need to be reviewed yearly. I need to create a report that shows us all of our past due contracts. However, these reports need to be sorted by:
    1: 0-30 days past due
    2: 30-60 days past due
    3: 60-90 days past due
    4: 90+ days past due

    I was able to calculate the days over due just fine, This just tells me how many days from the current day another day is.
    HTML Code:
    DateDiff("d",[Products]![Initial Date],Now())-365
    But I have no idea on how to group them, the way I need them. Any help would be great. I'm fairly new to the reporting part of access btw.

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    If you use an iif statement you can narrow down the results to display just the categories you want... from that you could then group them;

    iif(DateDiff("d",[Products]![Initial Date],Now())-365 < 30, "00 - 29 days past due",
    iif(DateDiff("d",[Products]![Initial Date],Now())-365 < 60, "30 - 59 days past due",
    iif(DateDiff("d",[Products]![Initial Date],Now())-365 < 90, "60 - 89 days past due", "90+ days past due")

  3. #3
    Join Date
    Sep 2011
    Posts
    5
    I figured that, but I guess my question is, where do I put that if statement.

    Thanks for the quick reply btw

  4. #4
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    I'd actually just slot it into the record source of your report along the lines of;

    Code:
    SELECT 
    IIf(DateDiff("d",[Initial Date],Now())-365<30,"00 - 29 days past due",
    IIf(DateDiff("d",[Initial Date],Now())-365<60,"30 - 59 days past due",
    IIf(DateDiff("d",[Initial Date],Now())-365<90,"60 - 89 days past due","90+ days past due"))) AS Grouping, 
    Products.[1], Products.[2], Products.[3]
    FROM Products
    GROUP BY 
    IIf(DateDiff("d",[Initial Date],Now())-365<30,"00 - 29 days past due",
    IIf(DateDiff("d",[Initial Date],Now())-365<60,"30 - 59 days past due",
    IIf(DateDiff("d",[Initial Date],Now())-365<90,"60 - 89 days past due","90+ days past due"))), 
    Products.[1], Products.[2], Products.[3];
    P.s. there are probably far more efficient ways to write the above, or even a more efficient way of generating these results but this is all I know immediately.

    Note that before my edit I had said to start the above with "SELECT Products.*"... unfortunately you can't Group these results if you use .* so in place just put the fields that you want to display on the report in place of Products.[1], Products.[2], etc.
    Last edited by christyxo; 09-29-11 at 11:44.

  5. #5
    Join Date
    Sep 2011
    Posts
    5
    how do I access the record source of the report? I don't know if your read the bottom of my first post but I'm extremely new to all this.

  6. #6
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    I didn't read that. Sorry.

    • MAKE A COPY OF YOUR REPORT FIRST!
    • Open your Report in Design View
    • Open your properties window (F4)
    • Change the Drop Down box in the properties window to say Form
    • Open the Data Tab
    • The Record Tab most likely says "Products" but you want to click on the "..." to open up the query builder
    • From the Products table, double click each item that you want on your report, then go to a new column in the bottom section of this page, and paste the following into the Field section of this column;

    Groupings: IIf(DateDiff("d",[Initial Date],Now())-365<30,"00 - 29 days past due",IIf(DateDiff("d",[Initial Date],Now())-365<60,"30 - 59 days past due",IIf(DateDiff("d",[Initial Date],Now())-365<90,"60 - 89 days past due","90+ days past due")))

    Save and see how that looks...

  7. #7
    Join Date
    Sep 2011
    Posts
    5
    That worked great. Thank you so much. I made a query using that, plugged the query into the report, then grouped by the results by just adding a group by the new field created. I just need to figure out titles for each group.

  8. #8
    Join Date
    Sep 2011
    Posts
    5
    So this is the code I used in the one column:

    Code:
    Groupings: IIf(DateDiff("d",[Products]![schedule_A_review_date],Now())-365<=30,"Current to 30 days past due",IIf((DateDiff("d",[Products]![schedule_A_review_date],Now())-365<=60) AND (DateDiff("d",[Products]![schedule_A_review_date],Now())-365>30),"31 to 60 days past due",IIf((DateDiff("d",[Products]![schedule_A_review_date],Now())-365>60) AND (DateDiff("d",[Products]![schedule_A_review_date],Now())-365<90),"61 to 90 days past due","90+ days past due")))
    I added some other things to try to fix the issue but it didnt help. Here's what I'm getting:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	9 
Size:	7.8 KB 
ID:	12211

    The days overdue are correct. But its not putting them in the correct grouping for some reason.

  9. #9
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    I don't see anything wrong with the code at all that would cause this to happen?

    I don't have Access on this laptop (I'm too stingy to buy it) but I'll have another play around at work tomorrow and see what I can do. Would it work for you if you rearrange the iifs?

    IIf(DateDiff("d",[Products]![schedule_A_review_date],Now())-365>90,
    "90+ days past due",
    IIf(DateDiff("d",[Products]![schedule_A_review_date],Now())-365>60,
    "61 to 90 days past due",
    IIf(DateDiff("d",[Products]![schedule_A_review_date],Now())-365>30,
    "31 to 60 days past due",
    "Current to 30 days past due")))

    The and statements aren't needed in your own though and it should work;

    IIf(DateDiff("d",[Products]![schedule_A_review_date],Now())-365<=30,
    "Current to 30 days past due",
    IIf(DateDiff("d",[Products]![schedule_A_review_date],Now())-365<=60,
    "31 to 60 days past due",
    IIf(DateDiff("d",[Products]![schedule_A_review_date],Now())-365<=90,
    "61 to 90 days past due","90+ days past due")))

Tags for this Thread

Posting Permissions

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