Phone Number Fields - Best Practice (1 Viewer)

waterdrop

New member
Local time
Today, 06:55
Joined
Apr 1, 2021
Messages
5
I have a simple phone number field in a database. I'd like to hear some data management best practice thoughts about how this type of field is best handled.

My own inclination is to use some sort of input mask for data entry, a text field for table storage of 10 characters and then some sort of output masks when displaying this data in a form or report. I'm sure most of you have much more experience than me thinking about this. Perhaps integers would be better than text for the table storage. Perhaps you can tell me what terminology Access developers use for input masks and output masks and thus I could look up the correct reference pages for those activities.

Thanks for your comments and my apologies if I don't seem to respond quickly, I haven't quite retired yet and work can get unpredictably busy!

~~waterdrop~~
 

Isaac

Lifelong Learner
Local time
Today, 03:55
Joined
Mar 14, 2017
Messages
8,738
I have usually just handled this as a 10-character text field. I personally do not like input masks for these on Form controls, I feel that input masks are VERY confusing to the end user - and annoying, as you have to worry too much about where the cursor is. In cases where I want the phone number to "look" a certain way, then I handle that as the user types (textbox Change event), formatting it on the fly.

It's up to you. Some people love input masks. I tend to think they are only exciting to a developer. :)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:55
Joined
May 7, 2009
Messages
19,175
better to use Text as datatype of the field.
you cannot put leading "0" when it is numeric.
as suggested earlier, simple text with no formatting/mask is better.
even without those "()-", you can still read the numbers and just
lookup the contry/area code (optional 2 fields).
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 21:55
Joined
Jan 20, 2009
Messages
12,849
My preference would be to separate the Country Code, Area Code and Number into three fields. (You could get funky and parse this from a single entry textbox.)

There are no leading zeros in this structure because the zero is actually the area access code, not part of the number or the area code. Hence the numbers can be stored as integers and easily tested against a numeric range.

This immediately keeps out the alpha rubbish (eg "mothers phone" or "work") that some people inevitably enter when left to their own devices.

Believe me they will. We have one database with free text phone fields where there is also a checkbox to indicate "Work" plus Location and Comment fields. We have numbers where operators have added "(W)" after the number, checked the Work checkbox and put "WORK" in the location. Then there are those who put "(H)" after the number and tick the Work checkbox then "mother's phone" in the Comment.

It also has a combobox for Telephone/Mobile/Fax. You guessed it, we have landline numbers flagged as Mobile.

Worse still the system exports any number flagged as Mobile but doesn't bother with validation before it is sent to a third party system. If someone uses anything but ten digits starting with 04, the import at the other end is rejected. Then we have to edit the export file and process it manually. The system allows customers to update their phone numbers on the internet and does absolutely no validation.

Some people shouldn't be developers or have anything to do with data yet work in the industry.
 

waterdrop

New member
Local time
Today, 06:55
Joined
Apr 1, 2021
Messages
5
Good morning and thank all of you very much. These are all good information for me. People coming at problems from different directions is a very powerful thing. Right away it reminds me that the goals of the data input person are different from the person looking at the field and that in some settings a phone number becomes a special work focus where a lot of attention is needed!

I may not use a mask but I do think I'll go looking for where Microsoft has documented this among the confusing locations of VBA refs, Access refs and DAO refs. ...well, it seems to be over in the Support set of docs!
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:55
Joined
Sep 12, 2006
Messages
15,614
Note that even if the "number" is text with a leading zero, if you send it to excel in a query, you may find it's still treated as a number.
And note also that opening a csv in excel and then saving it can change formatting of some data values, such as this.
 

waterdrop

New member
Local time
Today, 06:55
Joined
Apr 1, 2021
Messages
5
Oh, that's interesting too (the Excel tips) - to an old guy it seems almost surreal that the tools for programming back and forth between the Office (or should I say "Microsoft Apps for Enterprise" haha) applications seem so smooth and extensive now. That's part of what has me back playing with VBA, where I've dabbled off and on over the years.

Back to the mask thing (even though I may not really use it in this little project) I'm still too inexperienced to remember, even though I think I've read it: In a simple scenario I see that an input mask is a property on the data tab of a Text Box but I forget whether this would then store these extra dashes and parentheses and spaces in the text field of the bound table?
 

Users who are viewing this thread

Top Bottom