Alpha in string (1 Viewer)

[pekajo]

Hi,
I have had a strange request for a database I am writing. We have a string field for a phone number so we can keep the '0' at the beginning of a mobile number. However some people are adding land lines with words such as 'ext' etc. My question can I search for alpha in an alpha string so I can get rid of these words?
Peter

 

[plog]

You use an input mask:

Control data entry formats with input masks - Microsoft Support

Learn how to add input masks in Access to table fields, queries, and to form and report controls to help people entering data.

support.microsoft.com

The exact code is down the page, but give the whole thing a read.

 

[theDBguy]

Hi,
I have had a strange request for a database I am writing. We have a string field for a phone number so we can keep the '0' at the beginning of a mobile number. However some people are adding land lines with words such as 'ext' etc. My question can I search for alpha in an alpha string so I can get rid of these words?
Peter

Hi. Also, if you're trying to clean up existing data, try looking up the Replace() function.

 

[Isaac]

I'll throw in my approach, along with some reasoning.
First, I tend to find input masks somewhat confusing & annoying. Just my personal preference/experience. As an end-user, it's VERY easy to accidentally get the cursor in the 'wrong' spot, or even tell which spot it's inside, IMHO. (in Access). Once it gets in the wrong spot, instead of the characters properly back-filling into position, the whole thing gets messed up. (Maybe things have gotten better since I last tested them).

Second, I've noticed the technique I'm recommending a lot on websites, apps, etc., and IMO it's the easiest one from an end user perspective.
What it is: Code your control's Change event. In this code, refer to the .Text property. In this code, simply strip out disallowed characters.
Alternately, if you find it difficult to write this code (might involve some SelStart type of stuff), or if it comes across too weird to the end user, simply code the control's AfterUpdate event (this time refer to the control's .Value property), which will strip out disallowed characters after they exit the control.

I always like to evaluate my Access approaches against what end-users are most used to in other apps (particularly web ones) globally.

I see sites and apps with all sorts of approaches to contain phone numbers - including some with input masks and some with the technique I mentioned--But I think the very best ones are the ones where I type whatever the heck I want, and after I tab out of it, OR while I'm typing, it resolves to the precise format they want. Without me worrying where my cursor is "inside" that ugly mask. TurboTax.com's way of handling what I type into SSN, Phone, and Currency fields comes to mind--I can do them any one of about 5 different ways--they auto-magically resolve with no further ado.

PS - If you just want a really good laugh to lighten your day, check out this brief article about programmers and phone numbers and forms. Very funny! I was laughing out loud after the 2nd GIF.

 

[pekajo]

Hi,
Sorry I forgot to mention that this is imported data from an excel spreadsheet not entered on access.
After I wrote this post I thought I may try multiplying the field by 1 then use on error fix it.
PS Thanks for all the quick responses.

 

[arnelgp]

you can, get rid of the Alpha and only retain the number portion:

Public Function RemoveAlpha(ByVal p As Variant) As Variant
    If IsNull(p) Then
        Exit Function
    End If
    p = p & vbNullString
    With CreateObject("VBSCript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = "[^0-9]+"
        p = .Replace(p, vbNullString)
    End With
    RemoveAlpha = p
End Function

 

[CJ_London]

I have a very similar function but I have an option to leave spaces and other characters such a +,

to leave spaces and + my pattern looks like this "[^0-9, ,+]"

and final assignment would be

removeAlpha=trim(p)

to capture values such as +44 01545 123 456