Formula -Conversion error - checking if a field is numeric greater than 1 and is not null

[d1miller99]

Hi everyone

I have a field, MaybeZip, that might be a zip code number or it could be an address. The TONUMBER() is checking to see if it is A number greater than 0. 

IF TONUMBER([MaybeZip]) > 0 AND !ISNULL([MaybeZip]) THEN
	NULL()
ELSE
	TRIM([MaybeZip])
ENDIF

But, I can see how that would cause a conversion error because if "MaybeZip" contains characters, it would cause a conversion issue. I could do a test with IsInteger()  - check if the field is integer and then do a nested IF to see if it is null. But, that becomes ugly because i would have to use an "else" with both. 

Has anyone ran into this situation and not mind explaining how they solved it? Thanks!

[binuacs · Accepted solution]

@d1miller99 One way of doing this

[DataNath · Accepted solution]

Hey @d1miller99, from the looks of your formula, you're just checking whether or not [MaybeZip] is a number and if so, you want to null it? If so, you can just handle it with this expression. If it's a number, it'll null it, if the record is null then of course it'll stay null and if it's an actual address then it just gets trimmed as you have in your original expression:

Please let me know if I'm completely off the mark here!

[d1miller99]

Thanks everyone for your help! Using the regular expression solution as presented by Binuacs, I added additional logic: so this allows me to get an error if a field contains a 0 satisfying the > 0 criteria 

 IIF( REGex_MATCH(TRIM([MaybeZip]), '.*\D.*'),[MaybeZip], Null() )