UK telephone numbers

[wotme]

Hi, can you help. I want to be sure that a telephone entry conforms to the correct UK format. The problem is some codes are 3 digits and some are 5 digits. The number length is always 11, The formats are either xxx xxxx xxxx or xxxxx xxxxxx. How could I make sure the entry takes either format?

 

[noboffinme]

Hi wotme,

The 'Input Mask' is what I think you're referring to, this forces the format to comply to a certain pattern.

If that's correct, I don't think you'll be able to apply one format that suits 2 different formats.

If the info is being entered into a Form, why can't you choose one format only of the 2 you've described?

The other option is to not apply an Input Mask & write some VBA code to check the format complies with either format you want after it is entered.

 

[wotme]

Hi, thank you for your suggestion, and yes you are right, a input mask would be ideal, but as I require two formats that wouldn't be possible. I have written some code that works, or at least is part way there, it needs a bit of refinement, any suggestions for improvement would be appreciated.

Private Sub telno_AfterUpdate()
If InStr([telno], 2) = 2 Then
OldValue = Mid([telno], 1, 3) & " " & Mid(Replace([telno], " ", ""), 4, 4) & " " & Mid(Replace([telno], " ", ""), 8, 4)
Else
OldValue = Mid([telno], 1, 5) & " " & Mid([telno], 6, 7)
End If
Me.telno.Value = OldValue
End Sub

 

[wotme]

I'm pleased to say it works, I hope it will be of help to others. If anyone can improve it let me know

Private Sub telno_LostFocus()
Dim oldvalue As String
oldvalue = [telno]
If InStr([telno], 2) = 2 Then
oldvalue = Mid([telno], 1, 3) & " " & Mid(Replace([telno], " ", ""), 4, 4) & " " & Mid(Replace([telno], " ", ""), 8, 4)
Else
'OldValue = Mid([telno], 1, 5) & " " & Mid([telno], 6, 7)
End If
Me.telno.Value = oldvalue
End Sub