Phone Numbers Update (1 Viewer)

silentwolf

Active member
Local time
Today, 16:46
Joined
Jun 12, 2009
Messages
545
Hi guys,

just wondering how I could go about updateing phonenumbers the easiest way.

As with all Phone Numbers there are many different Area Codes and Country Codes

To get it all a little bit better formated what would be an easy way of doing so.

For example there are Austrian Mobile Numbers... something like 06441234245, but not always the same length but starting at least many of them with 06
For them I like to get those numbers so I can call it also when I am in Germany with the 0043 644 1234 245

So basically when it starts with 06 then add 0043 then a space then three characters then perhaps 4 then the rest.

Sure it can be done via code but I guess how to find all numbers starting with 06...

And then perform the action.

For other Numbers there are other rules so to speak so how do you guys go about it.

Is there a a way of using or running an input mask over it?

Cheers
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:46
Joined
Feb 28, 2001
Messages
27,001
Obviously, with varying prefixes having leading zeros, those numbers all have to actually be strings. Therefore, to find string prefixes, just use the LEFT() function to extract the part you wanted to find. E.g.

Code:
SELECT TheNumber FROM TheTable WHERE LEFT(TheNumber, 2) = "06" ;

Once you have this query, you can build on what you want to do. It can be the basis for an UPDATE query (if that is the way you want to go) or the basis for a VBA loop using a recordset that will only contain the desired numbers. If there is more to the data than just the number, your query can include other fields. The only down side to this is that you would have serious troubles for doing this in one complete query because your prefixes appear to be varying in length. I believe that a JOIN, where the "ON" element is variable length, would be rather complex and probably a bit frustrating to get right. So you would need multiple queries for this.
 

silentwolf

Active member
Local time
Today, 16:46
Joined
Jun 12, 2009
Messages
545
Hi Doc_Man,

thanks for your reply!

well that is what I got at present a very simply function..

Code:
Public Function AddAreaCode(phoneNr As String) As String
    Dim strOld As String
    Dim strNew As String
    strOld = "0"
    strNew = "0043"

    If phoneNr Like "06*" Then
        AddAreaCode = Replace(phoneNr, strOld, strNew)
    End If
End Function

not very flexible but it done the first part of the job.

With your idea is of course lot better will try that.. however
how can I add multible spaces in the text?

At present there are "phonenumbers" stored in text and with somthing like "004366412345678"
if I like to add at least after the first 4 characters a space, then "0043 664 12345 678"

so in this case it would be Space after 4,7,12 position of the original text "0043 664 12345 678"


Could you help me with this please?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:46
Joined
May 21, 2018
Messages
8,463
Code:
Public Function AddSpaces(ByVal StringIn As Variant, ParamArray spaces() As Variant) As String
  Dim I As Integer
  Dim location As Integer
  AddSpaces = StringIn & ""
    For I = UBound(spaces) To 0 Step -1
      location = spaces(I)
      If location < Len(StringIn) Then
        AddSpaces = Left(AddSpaces, location) & " " & Right(AddSpaces, Len(AddSpaces) - location)
      End If
    Next I
End Function


Public Sub Test()
  Debug.Print AddSpaces("004366412345678", 4, 7, 12)
End Sub

Since you are using this in a query, it handles a passed null field value. Also if the length of the string is less then where you are trying to split it will not split the record. With the paramarray you can specify any amount of splits and the locations for the splits.
 
Last edited:

silentwolf

Active member
Local time
Today, 16:46
Joined
Jun 12, 2009
Messages
545
Hi MajP,

thanks for your help I will test that in a little while but now I just realized that my function has disstroyed my phonenumber :( cry.. need to fix that first but will get back to you very shortly.

Cheers
 

Minty

AWF VIP
Local time
Today, 23:46
Joined
Jul 26, 2013
Messages
10,355
You could put the replacement values in a table, and use that to drive a bulk update based on a join.
If you create it all as a select query first then you won't physically mess with your original data.
 

silentwolf

Active member
Local time
Today, 16:46
Joined
Jun 12, 2009
Messages
545
You mean insert another field into the table first?

Add a field in the Contact Table and then do a select Query on that Field?

Not sure if I follow correctly..

Sorry
 

silentwolf

Active member
Local time
Today, 16:46
Joined
Jun 12, 2009
Messages
545
Data is set again.. what a mess lol.. (

Especially because the Data is now split compare to the original table... makes it again a little more difficult to get back the data once you stuffed it up as I did.. (
 

Minty

AWF VIP
Local time
Today, 23:46
Joined
Jul 26, 2013
Messages
10,355
If you created a table something like

OldPrefixNewPrefixDescription
060043 6Austrian Mobile
070044 7UK mobile

You could use a select query, joined to it to produce the replacement value.
Once you have that working you can change it to an update query.

This would process the whole recordset lot in one set-based operation.
 

silentwolf

Active member
Local time
Today, 16:46
Joined
Jun 12, 2009
Messages
545
You mean something like that?

If yes dont I need then a Like operator to find that and how does is continue?
 

Attachments

  • JoinTables.JPG
    JoinTables.JPG
    30.7 KB · Views: 139

silentwolf

Active member
Local time
Today, 16:46
Joined
Jun 12, 2009
Messages
545
Maybe someone could take a look at this sampe Database I am getting a little frustrated lol

Please :)
 

Attachments

  • UpdatePhoneNumbers1.zip
    17.2 KB · Views: 145

Gasman

Enthusiastic Amateur
Local time
Today, 23:46
Joined
Sep 21, 2011
Messages
14,048
I would tend to run a select query first to view the new numbers. After reviewing, if all looks good, change to an update query.
I would still take a copy of the table being updated first, just in case I missed something.
 

silentwolf

Active member
Local time
Today, 16:46
Joined
Jun 12, 2009
Messages
545
Hi Gasman,
yes I do understand what you are saying but how to I be able to have a select Query on a different Table like shown above?

Like with the Prefixes as Minty suggested?

Way to long on this for today it turns my head in..
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:46
Joined
Sep 21, 2011
Messages
14,048
My thoughts are just a simple query selecting old number and yourfunction(oldnumber), then review?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:46
Joined
May 21, 2018
Messages
8,463
Here is with a recordset. However I think I would be storing my data differently.
CountryCode
AreaCode
Number
Extension
Type (land line or mobile)

Then using the rules you can show the number the way you want.
I also believe the 00 before 43 is the Access number and depends from which country you are calling from.

Code:
Public Sub UpdateTable()
  Dim left2 As String
  Dim CtryCode As String
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("Select * from TblContacts where telcompanyNew is null")
  Do While Not rs.EOF
    left2 = Left(rs!telcompany, 2)
    CtryCode = Nz(DLookup("NewPrefix", "tblPrefix", "oldPrefix = '" & left2 & "'"), "")
    rs.Edit
    rs!telcompanyNew = CtryCode & " " & Mid(rs!telcompany, 2)
    rs.Update
    rs.MoveNext
  Loop
    
End Sub

tblContacts tblContacts

ContactNameTelCompanyTelCompanyNew
Hans Huber06454854580043 645485458
Erna Gruber06763515230043 676351523
Christa Neuhuber064432812120043 6443281212
Milly Miller0745245522350044 74524552235
 

silentwolf

Active member
Local time
Today, 16:46
Joined
Jun 12, 2009
Messages
545
Hi guys,

many thanks to all! MajP thanks alot for your help!!

I will look into it I just need to get a little away for a bit and than try it!

Will get back to you as soon as I calm down a little )

Cheers!!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:46
Joined
May 21, 2018
Messages
8,463
It seems to me it would be very helpful to save the area code and phone type in order to figure out how to split it up. Since there is not a set length in Austria.

If I understand the rules
1. Cell numbers do not use area code but land lines do
2. Cell numbers start with a 3 or 4 digit prefix that begins with a 6
Mobile Providers with own Networks
ProviderCode
Hutchison Drei660 and 699
A1 Telekom Austria664
T-Mobile676


Mobile Providers without own Networks
ProviderNetworkCode
Telering1T-Mobile650
BoB2A1680
yesss!2A1681 and 6998
Eety2Hutchison Drei665
Tele2A1688
VectoneT-Mobile688
HoTT-Mobile677
SpusuHutchison Drei670
Lidl ConnectHutchison Drei690

3.But some area codes begin with a 6 just to be confusing.
4. Both the area codes and the subscriber numbers are variable length with numbers then from 4-13 digits in length.

So without specifically storing the area code and phone type, I am not sure how you would know where to split up the number.
So I think it would be helpful to store some of this in separate fields.
 

silentwolf

Active member
Local time
Today, 16:46
Joined
Jun 12, 2009
Messages
545
hmm it is confusing I know so many different types of numbers with no set rules that is just in austria lol

But also keep in mind that I most properbly like to send that data to Outlook Contacts so I am not sure if splitting them in seperate area code is good?

Don't like to make it more complicated than it is already..

What are your thougths on that MajP ?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:46
Joined
May 21, 2018
Messages
8,463
In the previous thread you mentioned you wanted to split it up at 4,7,12. I assume you really would want to split it into some logical groups like
Country code, area code if exist, cell code if exists, and user number. But since that is all variable length you may or may not split at 4,7,12.

If you wanted to take any raw string and then split it after the fact it is going to be hard to determine logical splits.
You had this number
00436441234245
And wanted it split like this
0043 644 1234 245
Coming up with a rule to do that would be hard. In this case 644 I guess is the cell code. But it could be part of an area code.
So to split it you may not be able to run one query or code. You might need several queries based on the total length and other critieria.
 

Users who are viewing this thread

Top Bottom