Results 1 to 9 of 9

Thread: Report Question

  1. #1
    Join Date
    Jan 2010
    Posts
    7

    Unanswered: Report Question

    Hi Everyone.

    In my customer table, I have a expired date. This field would fall into 1 of 3 status.. null, < Today or > today. I'm trying to create a simple report with three lines:

    Null Records = X
    Expired Records = X
    Current = X

    Any ideals on the best way to do this?

    Thanks,
    Kevin

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What is X? Is it a count of these?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Assuming so:
    Code:
    SELECT SUM(SWITCH([expired date] IS NULL, 1))  AS [Null Records], SUM(SWITCH([expired date] = "< Today", 1))  AS [Expired Records]
    FROM daTable
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2010
    Posts
    7
    Yes, X is the total number of records...

    I'm still a little new to Access, so I have a report with 3 unbound boxes...

    Where would I use the statement you provided.

    Thanks,
    Kevin

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Pootle Flump's solution uses a query to add up all the three scenarios you have. The query would then be used as the report's Record Source.

    I agree with Pootle's approach. You will need to do any calculations before it gets to the report in one or more queries.

  6. #6
    Join Date
    Jan 2010
    Posts
    7
    I edit the code to use my names and paste it in the SQL view, but when I run it I get a data mismatch.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is a date column isn't it?
    Code:
    SELECT SUM(SWITCH([expired date] IS NULL, 1))  AS [Null Records], SUM(SWITCH([expired date] < Date(), 1))  AS [Expired Records
    FROM daTable
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2010
    Posts
    7
    working... maybe I did something wrong.

    thanks for the help.

    Kevin

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No, you did nothing wrong. I took your first post literally. If you read the code in my first answer you will see what I did.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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