How to find phone nbr fields populated incorrectly or null 2

[NewToThis2]

CR V9
I need a formula that will identify phone numbers entered incorrectly or missing. I need something to cover incorrect examples such as:
- phone number using periods instead of dashes such as 987.120.9852
- phone numbers using all zeros such as 000.000.0000
- phone numbers missing an area code
- phone number field is null.

The correct way this field should be populated is:
(582)987-5098. The string field name is PAEMPLOYEE.HM_PHONE_NBR

I would appreciate any assistance. Thanks so much!

 

[lbass]

You could try something like the following for a record selection formula:

isnull({PAEMPLOYEE.HM_PHONE_NBR}) or
{PAEMPLOYEE.HM_PHONE_NBR} = "(000) 000-0000" or
len({PAEMPLOYEE.HM_PHONE_NBR}) < 14 or
instr({PAEMPLOYEE.HM_PHONE_NBR},".") <> 0

This assumes you want a space after the closed paren. If not, reduce the number from 14 to 13 above.

-LB

 

[mrudolph]

Double check the functions available to you in your SQL expression Editor. You may have functions available there to do the len() and instr() parts of the formula lbass gave. If available this will allow you to create SQL expression fields to use in your record selection so those parts are passed to the SQL statement for the server to process.

MRduolph

 

[NewToThis2]

Thanks so much! I ended up using
not ({PAEMPLOYEE.HM_PHONE_NBR} like "***-***-****")