#ERROR question

  • Thread starter Elmtree via AccessMonster.com
  • Start date
E

Elmtree via AccessMonster.com

My formula:

Mid([f1],InStr(10,[f1],": ")-1)

returns #ERROR if the value is blank, otherwise works fine. Any ideas how to
change my formula to get beyond the #ERROR?

Thanks

mike
 
J

John W. Vinson

My formula:

Mid([f1],InStr(10,[f1],": ")-1)

returns #ERROR if the value is blank, otherwise works fine. Any ideas how to
change my formula to get beyond the #ERROR?

Thanks

mike
Click to expand...

IIf(InStr([f1], ": ") = 0, "", Mid([f1],InStr(10,[f1],": ")-1))
 
J

Jerry Whittle

Mid doesn't play well with 0, negative numbers, or nulls.

IIf(Len([f1])>10, Mid([f1],InStr(10,[f1],": ")-1),Null)

Or maybe

IIf(InStr(10,[f1],": ">10, Mid([f1],InStr(10,[f1],": ")-1),Null)
 
Top