Data format problem

[Al Kramer]

I have created an access database by importing external data. The data i have imported has the phone field stored with its format ie 999-999-9999.
I know this is not best practice but if i store my new data raw I will have the data store in differnt ways. I'm thinking this will cause future problems.
How can I store my phone number as it is formatted?

 

[MajP]

You can decide how you want to store phone numbers and if you want to store them differently then you can do an update query on those records and put them in the format you like.

 

[The_Doc_Man]

How can I store my phone number as it is formatted?

During the import process, if you have to trouble-shoot the input number to assure its compliance with a desired format, the time to do that is when it first enters the DB. While it is certainly possible to come back later to reformat a field, the best way to stop from having to do that is to trap bad formats on first arrival. Unfortunately, there is no simple setting to force this compliance. All you can (easily) test is "compliant or non-compliant." You will need some VBA code after determining non-compliance. If you are doing this import process in bulk (perhaps with TransferSpreadsheet or some similar operation), I think you are stuck in going back to fix things after import.

 

[Pat Hartman]

"Standard" format for a US phone number used to be (000) 000-0000. Now, I see a lot as 000-000-0000 or (000)000-0000. If you store the data without the formatting characters, it is trivial to change the output. If you have to store phone numbers from multiple countries, the problem is bigger and I would tend to break up the number into parts so the country code prefix is in its own field and then you have different input masks you apply depending on the country code.