Results 1 to 8 of 8
  1. #1
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91

    find oldest letter in table

    Hi all,



    I'm new to the forum :-)

    I'm working on a query that will allow me to put the oldest letters we are working on, on the top.
    We receive a request from our client and once we start working on the request, we fill in the field [Start_Date]. If we have some questions to our client, we send an email and put the sent date in the field [date_sent_question_client]. When we receive the answer, we fill in the field [date_answer_from_client]
    As you can see from the below, the calculation of the days passed are almost OK, but since the sending and receiving answer was 2 days in the first letter, those 2 days should be descounted and instead of 10 it should be 8:
    [Start_Date] [date_sent_question_client] [date_answer_from_client] Expr1: Date()-[Start_Date] Expr2: [date_answer_from_client]-[date_sent_question_client] Expr3: [Expr1]-[Expr2]
    30/06/2015 30/06/2015 02/07/2015 10 2 8
    30/06/2015 10
    02/07/2015 8
    02/07/2015 8
    02/07/2015 8

    Any clue how it could be solved?

    Greetings.

  2. #2
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi all,

    I added the database so you will be able to see the query I mean.

    Greetings.FindOldestLetter.mdb

  3. #3
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi all,

    is this query so complicated?

    Greetings.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    So exactly what is the issue? You want the oldest record for each client? Review: http://allenbrowne.com/subquery-01.html#TopN

    Posting reply to your own thread excludes it from the 'Unanswered' search and got less attention. I almost missed it because it was low in the list of Today's posts.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi June7,
    All I need is to get the oldest record on top. If the 2 fields [date_sent_question_client] and [date_answer_from_client], they should be descounted from the days that are in Expr1: Date()-[Start_Date]
    as in post nr 1, the first on top has 10 days in "Date()-[Start_Date]" but after taking the 2 days of, it should be 8 and then it needs to move down to the second possition. And the one in the second possition will be the one with the most days and move to the first possition, llike below:
    [Start_Date] [date_sent_question_client] [date_answer_from_client] Expr1: Date()-[Start_Date] Expr2: [date_answer_from_client]-[date_sent_question_client] Expr3: [Expr1]-[Expr2]
    30/06/2015 10
    30/06/2015 30/06/2015 02/07/2015 10 2 8
    02/07/2015 8
    02/07/2015 8
    02/07/2015 8

    I hope this is more clear now.

    greetings.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Arithmetic with Null results in Null. Perhaps you simply need to handle the nulls so every record will calculate a value for use in sorting.

    Expr2: Nz([date_answer_from_client], Date())-Nz([date_sent_question_client],Date())

    or just

    Expr3: [Expr1]-Nz([Expr2],0)

    or use Is Null - review http://allenbrowne.com/QueryPerfIssue.html#Nz
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi June7.

    You saved me with that one :-)
    The only thing is that when I try to so sorting in Expr3, all 3 expressions ask me for a parameter and after just pressing OK I get the query result but Expr3 is not sorted at all.
    Any idea what goes wrong?

    Greetings.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I think the Expr3 would have to repeat the first two expressions and apply filter criteria to that constructed field:

    (Date()-[Start_Date]) - ([date_answer_from_client]-[date_sent_question_client])

    Otherwise, use the query in a report and set sort criteria in report design.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 02-21-2014, 05:03 PM
  2. Replies: 4
    Last Post: 02-09-2014, 01:52 PM
  3. Replies: 4
    Last Post: 09-18-2012, 05:07 AM
  4. Find first Capital letter in string
    By Dutch1956 in forum Programming
    Replies: 5
    Last Post: 09-07-2011, 03:14 PM
  5. Sort oldest to newest on form
    By Desstro in forum Forms
    Replies: 6
    Last Post: 09-18-2010, 05:35 AM

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