J JMichaelM Registered User. Local time Today, 02:15 Joined Aug 4, 2016 Messages 101 Feb 3, 2017 #1 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)
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 AWF VIP Local time Today, 10:15 Joined Jul 26, 2013 Messages 10,670 Feb 3, 2017 #2 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 Code: IIf(Left([FunctionString],InStr([FunctionString],", ")-1) is Null, 0, Left([FunctionString],InStr([FunctionString],", ")-1)) Gets messy doesn't it.
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 Code: IIf(Left([FunctionString],InStr([FunctionString],", ")-1) is Null, 0, Left([FunctionString],InStr([FunctionString],", ")-1)) Gets messy doesn't it.
C CJ_London Super Moderator Staff member Local time Today, 10:15 Joined Feb 19, 2013 Messages 17,558 Feb 3, 2017 #3 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))
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))
J JMichaelM Registered User. Local time Today, 02:15 Joined Aug 4, 2016 Messages 101 Feb 3, 2017 #4 Perfect it worked. Thanks a million as you saved me much time.