Search code examples
ms-accesstrim

MS Access InStr Trim #FUNC! Null

Using Trim and InStr, I have written an MS Access query to extract data from a field. The query works as designed to extract the desired data, however I receive a #FUNC! error when the field I am extracting from is blank.

I have attempted nz and an IIF statement - neither worked.

Current code:

ExprA: Trim(Left([Target],(InStr(1,[Target],"=")-1)))

Any ideas on how to edit this query to prevent the #FUNC! error from appearing for blank/null values?

Thanks!

Solution

  • You can "cheat" a little:

    ExprA: Trim(Left([Target],(InStr(1,[Target] & "=","=")-1)))