MySQL Forums
Forum List  »  Microsoft Access

Re: MySql and lost formatting
Posted by: Jonathan Shaltz
Date: October 12, 2005 11:43AM

There are two easy ways to handle this.
The best way is to keep storing phone numbers as integers (this keeps the files small and index-able), and use a function to format them. Note that to store ten digits, you will need to use DECIMAL(10, 0) rather than INT.
You can set the format of an Access control to "000-000-0000," or similar. In an Access query, you can use the function "format([FieldName], "000-000-0000")." If you're using this database online, I wrote this PHP function to check that a phone number is valid and format it with dashes:

function FormatPhone($Input)
if ($Input == "") return "";
if (is_numeric($Input) == FALSE) return $Input;
if ($Input == 0) return "";
if ($Input > 9999999999) return "";

$TextForm = strval($Input);

if (strlen($TextForm) <= 7)
while (strlen($TextForm) < 7) $TextForm = "0" . $TextForm;
return substr($TextForm, 0, 3) . "-" . substr($TextForm, 3);

while (strlen($TextForm) < 10) $TextForm = "0" . $TextForm;
return substr($TextForm, 0, 3) . "-" . substr($TextForm, 3, 3) . "-" . substr($TextForm, 6);

The second way is to change your PhoneNumber fields to CHAR(14). The downside of this is that different people store phone numbers differently, and your data can be messy. Because of this, you will not be able to easily search or sort the data. Finally, it will take up more space.

Hope this helps!

Options: ReplyQuote

Written By
October 04, 2005 04:23PM
Re: MySql and lost formatting
October 12, 2005 11:43AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.