Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    4

    Unanswered: Report - need last completed date

    Hi,

    I've been working on this for awhile now and can't seem to figure it out:

    I have a report where there is a Key_ID field that can be many.
    Each record with the same Key_ID has a "completed date" field called date.
    Sometimes the Date field is populated (when the work is done for that month) and of course future records with a "Target Date" field have a blank Date field.

    I'm trying to list only the last completed date from the Date field on the report. I don't want more than one record per Key_ID.
    Also the next scheduled date (so the next record after one with the same Key_ID) with the records Date field blank.

    How do I get the most recent record that has the Date field populated only?

    The table I'm reading has a Target Date (Always filled in with some date)
    and Key_ID (to match with other table for services, always filled in)
    and Date (when the item was completed)

    Does this make any sense? No wonder I'm confused?!?!?

    PLEASE help!!!

  2. #2
    Join Date
    Jul 2004
    Posts
    64
    Where are you getting the data for the report from? This seems like it would be easily solved by using a query as the recordsource and using a "Max" function for the date within the query

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    SELECT TOP 1 [Target Date], CompletionDate FROM MyTableNameHere WHERE (Key_ID=Something) ORDER BY CompletionDate DESC;

    This is one way ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Aug 2004
    Posts
    4
    Thanks for the answers, as usual, the process has been changed so now I don't need to do this but I will try these when I get time to see what works.

    Thanks Again!!

Posting Permissions

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