UK telephone number masking

[akfruit]

All UK mobile numbers are as follows:-

XXXXX XXX XXX (11 digits)

e.g 07747 340 147 (11 digits)

Can someone help me so that when i put in XXXXXXXXXX this gives me a result of (XXXXX) XXX XXX or XXXXX XXX XXX

Thanks in advance.

[ktstem]

Hi akfruit!

To get (XXXXX)XXX XXX

"(" & Left( Abs(Phone Number Field), 5) & ")" & Middle( Abs(Phone Number Field), 6, 3) & " " & Right( Abs(Phone Number Field), 3)

To get XXXXX XXX XXX

Left( Abs(Phone Number Field), 5) & " " & Middle( Abs(Phone Number Field), 6, 3) & " " & Right( Abs(Phone Number Field), 3)

HTH,

Kate :P

[akfruit]

Kate

When I try both of these it leaves off the zero (eg 07737) and doesn't put in the correct telephone number.

Any idea why this is?

Thanks.

Martin

[ktstem]

Martin,

Alright this is where an FM God I am not! I know I had this problem before with a leading "0" and I think I figured it out, but I'm drawing a blank this morning. I'll keep trying, but does anyone else know why this happens?

Kate :P

[Søren Dyhr]

Lets establish one fact the resulting format are bound to be "text" to behave most predicatble!

My stab at it would be this:

"(" & Replace ( Replace ( Left ( entry ; 11 ) ; 9 ; 0 ; " " ) ; 6 ; 0 ; ") " )

...and please note that last paranthesis if followed by a inside the quotes!

--sd

Edited May 16, 2006 by Guest

[akfruit]

Excellent that works treat.

Thank you

[ktstem]

As usual...I'm impressed and still have sooooo much to learn!

Kate :P

[comment]

Just to clarify: Abs() is a numerical operation, so its result will always be a number, with no leading zeros.

Abs ( "05" )

returns 5.

Use the Filter() function to strip any non-numerical characters from the input.

[Søren Dyhr]

Just to clarify: Abs() is a numerical operation

Thanks for ironing out my convoluted expression! Indeed it should be said.

--sd

[akfruit]

Hello Folks

Further to my post yesterday to say that it works. I have encoutered a little problem.

When I open a new record Iget "()" automatically showing in the field.

Anybody know why this is happening?

Thanks

Martin

[Genx]

Yeh add an if expression in there to check if the length is correct, otherwise it tries to format a non existant number.

[Søren Dyhr]

Yes the checkbox in lower left corner have been deselected... It makes most sense to have it checked to deal with NULL values!

--sd

[Genx]

ooooh, right, forgot bout that :P