Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2008
    Posts
    151

    Unanswered: Help with Converting Neg Number to "0"

    Hello,

    I have an Access query that I data connect to Excel for a report. In the column Duration Start/Completion I have the formula =[@[Completion Date]]-[@[Start Date]] to calculate the number of days from start to completion. All is good, but when the completion date is blank I get this number -42992.

    How can I turn the negative number (-42992) into a zero (0)?


    Start Date Completion Date Duration Start/Completion
    9/14/2017 9/14/2017 -42992

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,477
    Provided Answers: 11
    try a a iif in the query some like

    think of the iif as the if in execl



    =IIF(IsDate([completion date]),[@[Completion Date]]-[@[Start Date]],0)
    Last edited by myle; 09-16-17 at 01:25.
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

Posting Permissions

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