Elimate #Func! Error in Query

[JMichaelM]

How do I add the IfError or NZ for the ones that are an error or blank so it wont show #Func!?

Function: Left([FunctionString],InStr([FunctionString],", ")-1)

 

[Minty]

I don't think I would use the word Function as the alias for a calculated field, as it's a reserved word used for Functions funnily enough...

And I hate to say it but this this would have been a lot easier in a function.

You would need to check for null before applying the logic, so something like

IIf(Left([FunctionString],InStr([FunctionString],", ")-1) is Null, 0, Left([FunctionString],InStr([FunctionString],", ")-1))

Gets messy doesn't it.

 

[CJ_London]

you can't get a left return value if there is no 'comma space' and the minimum value you can use is 1 - which means instr must return 2 or more.

Would think you can use the same functionality you are using in your other (solved) post

but basically I would think something like

Function: iif(InStr(nz([FunctionString]),", ")<2,"no comma space",Left(nz([FunctionString]),InStr(nz([FunctionString]),", ")-1))

 

[JMichaelM]

Perfect it worked. Thanks a million as you saved me much time.