Exception Report - Script question

[stuwannop]

HI Everyone

Hope someone can help. In my script I look in a particular field and report "Exception" or "OK" depending on whether a field is blank or not. Formula is simply this:

if(TSGRef = '','Exception','OK') as NoRefException

What I want to do is create a line that will check the "TSGRef" field to see if it contains only 5 numeric characters (essentially a number between 00001 and 99999 put another way.) I would like it to return "Exception" if the data in that field does not contain data matching that criteria.

Anyone help out with this?

Thanks in advance.

Stu

[swuehl · Accepted solution]

if(TSGRef >= 1 and TSGRef <= 99999, 'OK','Exception') as NumberException

edit: maybe add a len() check

if(TSGRef >= 1 and TSGRef <= 99999 and len(TSGRef)=5, 'OK','Exception') as NumberException

[stuwannop]

Thanks that's brilliant - I had got the greater than and less sussed just then and it was the LEN check that I didn't know how to do. Many thanks.