Hi Everyone!

This is such a simple thing, but for some reason I cant figure out how to do it, nor can I find any info online about this.

How do I validate a field using a calculation to remove spaces or carriage returns in a field.

For example lets say I type "hello 123", I want it to automatically appear in the field as "hello123", or if I type "hello123 ", I want it to appear as "hello123"

Thanks!

Removong spaces you could use an auto-enter, replace existing value of

Substitute(Text;" ";"")

and if in addition you want to remove carriage returns

Substitute(Substitute (Text ;" " ;"");"¶";"")

I was thinking something along the lines of validating the field with a calculation. What would the exact calculation be?

I tried validating the field using this calculation: [color:red]Substitute(Text;" ";""), but it still doesnt work.

Validating will not change the data; it will alert the user that the data is invalid and must be changed.

An auto-enter calculation with 'do not replace existing value' deselected WILL change the data as required, without alerting the user that the original data was invalid.

If you still would rather validate than automatically correct the data, use

not (Position( fieldName; " "; 0; 1 ) or Position( fieldName; ¶; 0; 1 ))

or

not (PatternCount( fieldName; " " ) or PatternCount( fieldName; ¶ ))

Thats brilliant. I'm sorry I didnt realize that validating would only warn someone. Changing it automatically via the auto enter is a much better solution. Thank you so much!

One more quick thing.

If I want to just delete a leading or trailing space in another field, how would I approach that?

Edited August 31, 200520 yr by Guest