Máté Juhász Máté Juhász - 6 months ago 10
SQL Question

Strange behaviour of IsError

I have a column with dates formatted as text, missing values are marked with

-
.

I try to create an expression (SQL, not VBA) which converts that column to date, converting missing values to
0
:


  • This expression works as expected:

    IIf([column]="-",0,CDate([column])

  • However if I try to make somethin more generic, looking for all non-date inputs I get
    #Error
    for all non-date:

    IIf(IsError(CDate([column])),0,CDate([column])



What I'm missing here?

Answer

I would try and use isDate() as an alternative:

IIf(IsDate([column]),CDate([column],0)

Notice that I have swapped the true and false part around in the iif() as the expression has changed.

Although, if the only alternative to a date is the dash - symbol, which you have used in the first expression I believe that the IsNumeric() function would also work.

Comments