Phone Numbers Update

[silentwolf]

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]

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.

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]

Hi Doc_Man,

thanks for your reply!

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

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]

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.

 

[silentwolf]

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]

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]

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]

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]

If you created a table something like

OldPrefix	NewPrefix	Description
06	0043 6	Austrian Mobile
07	0044 7	UK 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]

Ok thanks I will try hope I can make it work...

 

[silentwolf]

You mean something like that?

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

 

[silentwolf]

Maybe someone could take a look at this sampe Database I am getting a little frustrated lol

Please

 

[Gasman]

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]

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]

My thoughts are just a simple query selecting old number and yourfunction(oldnumber), then review?

 

[MajP]

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.

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

ContactName	TelCompany	TelCompanyNew
Hans Huber	0645485458	0043 645485458
Erna Gruber	0676351523	0043 676351523
Christa Neuhuber	06443281212	0043 6443281212
Milly Miller	074524552235	0044 74524552235

 

[silentwolf]

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]

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.

How to call Austria: country code, area codes, phone number examples

Complete resource on dialing to Austria: country code, mobile and geographic area codes, phone number format and other helpful information

www.howtocallabroad.com

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

Provider	Code
Hutchison Drei	660 and 699
A1 Telekom Austria	664
T-Mobile	676

Mobile Providers without own Networks

Provider	Network	Code
Telering1	T-Mobile	650
BoB2	A1	680
yesss!2	A1	681 and 6998
Eety2	Hutchison Drei	665
Tele2	A1	688
Vectone	T-Mobile	688
HoT	T-Mobile	677
Spusu	Hutchison Drei	670
Lidl Connect	Hutchison Drei	690

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]

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]

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.