Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Answered: iif to evaluate isdate and null the #error

    I am running a query that captures a string to get the date

    I then want to sort the alias descending but it will not sort because the #error insome fields will trip a type mismatch

    Is there a way to write an immediate if to say if this function is a date show the date but if it returns #Error make the filed blank?

    Her is the column alias that returns dates and #Error for not dates and it works fine but I want to get rid of the #Error

    MyEval:CDate(Right(Left([Notes],15),10))


    so things like "Sub: 09/0/2/2016 2/2016" returns 9/
    and things like "Sub: Nov. - Open: Dec." is returning the #Error which is what I want to just show as null

    I trired wrapping the above like this
    iif(isDate(CDate(Right(Left([Notes],15),10))),CDate(Right(Left([Notes],15),10)),Null)

    but it still failed

    Thank you for the help
    Dale Houston, TX

  2. Best Answer
    Posted by ranman256

    "=Iif( IsDAte(Right(Left([Notes],15),10)), Right(Left([Notes],15),10)),"")"


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    =Iif( IsDAte(Right(Left([Notes],15),10)), Right(Left([Notes],15),10)),"")

  4. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Perhaps this would be simpler

    =Iif(IsDate(Mid([Notes],6,10)), Mid([Notes],6,10),"")

    ??

    MTB

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    would it not be smarter to push this into a function?

    i think the problem you are hitting with your current code is that the iif(isdate... will always return true as de facto you've already coerced the value to be a date with cdate

    Code:
    iif(isDate(Right(Left([Notes],15),10)),CDate(Right(Left([Notes],15),10)),Null)
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    I used ranman's solution first and it was good (had to remove an extra paren but it was good

    I used that one because it was in line with the direction I was headed

    Totally understand the cdate issue , and it does cause problems

    I can now sort on that column as well which was essential

    thank you all for the comments
    Dale Houston, TX

Posting Permissions

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