Phone Numbers Update

[MajP]

Also you can try this query.

UPDATE tblContacts,
       tblPrefix
SET tblContacts.TelCompanyNew = [newPrefix] & " " & Mid([telcompany], 2)
WHERE tblPrefix.OldPrefix = Left([telCompany], 2)

 

[silentwolf]

well I guess I was looking to have this a little more uniform in that matter.
But as you corretly mentioned it is rather difficult wenn it is not set to a specific rule.
And yes that number I was refering to is a Cell number or the format of it at least

It is for a application where I would at least be able to have the correct "Country" Code in front of a Cell Phone or a Landline or Number.

But I will give it a shot what you suggested and see how the data will come out.

Cheers

 

[Minty]

Sorry, I was away last evening( here), your join would need a "Like " to work.

You can't use the query by design (QBD) normal access window to make that but you can write it in the SQL window.

 

[Auntiejack56]

To help Android and other application developers answer the question "Is this a valid telephone number?" more easily, Google developed and helps maintain an open source Java, C++, and JavaScript library for "parsing, formatting, and validating international phone numbers", libphonenumber.

This is a very complex area, and one option would be to get the C# version of the above library, and create a DLL that will format the numbers correctly.
That might be daunting for you, but possibly a straightforward task for a helpful colleague, if you work in a large shop. And before you ask, yes, it was too daunting for me, otherwise I'd share the result.

Jack

 

[Gasman]

Strangely enough, someone was looking to do the same thing, and reckons this is what they need from a link they posted, just do not know how to use it?

Microsoft Access: Strip phone numbers from feed that are all over the place

In Microsoft Access, I am receiving a feed of phone numbers which need to be processed and conformed into a standard format for post-processing in another system. The numbers I am given are all ove...

stackoverflow.com

Edit: Ignore this, seems to only do simple tasks

 

[strive4peace]

hi @silentwolf,

on InputMask for phone numbers ...

keep in mind that InputMask only has bearing when values are added or modified ... no bearing at all just for showing data, which is a good thing!

what I do is store InputMask for each country where there might be data. Then on the Current event of the form, you can modify InputMask for the phone number. If country is unknown, remove the InputMask and let user input however they want.

InputMask also modifies values for an Update Query if symbols are stored.

I generally store symbols in the InputMask specification (short text field) so that when data is output, it has the mask characters too

the Second parameter of InputMask "Specifies whether Microsoft Access stores the literal display characters in the table when you enter data"

0 = store literals
1 or blank = don't store -- while the symbols can show in Access, if data is exported, the symbols will be gone

parameters are delimited with ;

!\(999") "000\-0000;0;_

means (1) value starts from the right using the specified code ,
(2) literal symbols will be stored, and
(3) _ will be used to display where a character should be entered

~~~
if you know the country, then you can use your lookup table for countries to supply that information in the InputMask for them too ~

 

[Galaxiom]

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

The leading zeros are not part of the number. They are the Trunk or International Access codes. This is a widely misunderstood concept.

06441234245 will be made up of:
0 = trunk access code
644 = mobile area code for A1 telekom (other Austrian mobile providers also have three digit area codes starting with 6)
The rest is the number.

From Germany:
00 = German International Access Code
43 = Austria Country Code
644 - Area code

There is no zero between 43 and 644 because it is the trunk access code and hence is not part of the number at all.

Landline area codes in Austria have four digits. See the link below.

That International mobile number would be generally presented as +43 644 1234245 though don't store the +.
The + means International Access Code which varies between countries.

In Australia our International Access Code is 0011. Our country code is 61 so we write +61 for our international numbers.
To dial your Austria number from Australia we enter would enter 0011 43 644 1234245
You can see how storing either of the access codes in the number would be a big mistake.

Australian area codes are a single digit but most people think our area codes are two digits and they all start with zero.

Very few people here understand that our mobile numbers all start with 04 because 4 is the mobile area code. All our actual numbers whether mobile or land lines have 8 digits. In Australia we can call numbers in the same area without the area code (other than mobiles despite the phones recognising the pattern). I expect the same would be the case in most countries.

Think about the numbers in these terms. Store the Country code and Area code separately even if you parse them from a single control. This can sometimes be a good strategy when the entry operators don't understand how it works.

Concatenate them according to the location context by recording the local international and trunk access codes, country code and area code. This makes your phone number database international and the number to dial can be generated where ever it is used by simply changing the local country code, area code and access numbers. This is a very common way to handle the storage and generation of the numbers to be dialed when working with international numbers.

Telephone numbers in Austria - Wikipedia

en.wikipedia.org

 

[CJ_London]

in the UK mobile number have a 5 digit area code starting 07. Other area codes start with 0 and can vary in length from 3 to 6 digits whilst the phone number itself can vary from 4 to 8 digits - basically the longer the area code, the shorter the phone number but combined can vary in length between 9 and 11 digits.

phone numbers greater than 5 digits in length are often split into groups of 3 or 4 digits for ease of reading.

Telephone numbers in the United Kingdom - Wikipedia

en.wikipedia.org

 

[silentwolf]

Hi to all!

Many thanks for all of your input and thoughts on this matter!

I had do carry on with other things urgently but I will try to take all in and find a solution with help from you guys I am sure.

Hope I find time later to have a better look at it.

Many thanks really appricated!

 

[Galaxiom]

in the UK mobile number have a 5 digit area code starting 07. Other area codes start with 0 and can vary in length from 3 to 6 digits

Read what I explained above your post. The zero is not part of the number. It is the trunk access code. It even says that early on the page you linked but unfortunately it goes on to repeat the persistent myth about area code starting with zero.

It does serve to demonstrate just how poorly understood the phone number construct is among the general population.