Dashes in phone numbers and zip codes

[JMack]

When creating reports and queries, Access has the random tendency to fail to mishandle placement of dashes in phone number fields and zip code fields. This occurs even though the master table data field was configured correctly.



A phone number like 555-555-5555 may (or may not) come out as 5555555555.
A zip+4 code may come out as 67502-5555 or 675025555.
A 4-digit zip may come out correctly like 67502, or it may come out as 67502- .

There seems to be no pattern to these occurrences, and I have had this occur when exporting data to Word or Excel.

This is especially annoying when doing a mail merge with several hundred records. The only way I know to correct it when it happens is to edit each letter or envelope, making needed individual corrections manually.

Is there a work-around that will eliminate this problem?

[pdebaets]

Well, when exporting, you can use the replace function to remove the dashes. Ex.

Select replace(MyField,"-","") as MyFieldWithoutDashes
from MyTable;

When inputting the data, you can specify an input mask, so your data should appear in the table without dashes. You may have legacy data that was entered with no input mask. This might explain the existence of some of the dashes.

[ssanfu]

Well, when exporting, you can use the replace function to remove the dashes. Ex.

Select replace(MyField,"-","") as MyFieldWithoutDashes
from MyTable;

You could also run an update query using the Replace() function to remove the dashes from the phone number fields and zip code fields.

[JMack]

When exporting, I always do what you suggest when it is possible. However, when the phone numbers and zips are different it isn't quite that easy. Even the wild cards for "find" don't work well for "replace."

The problem occurs within Access when creating queries and forms regardless of the input mask. When one merges 273 or 500 records (or whatever number) it is totally impractical to edit each result individually.

[dreday]

Create a function to check the report field while it's being produced. You can then set the report field to = makephoneokay([phone])

Function makephoneokay(byval phonenumber as string) as string
....
process the different variants here
....

makephoneokay = [processed phone #]

End Function

That way you can control for several abnormalities in the phone numbers, and later in the zip codes

[JMack]

OK, lets have another go at this. I know that some believed they answered this three years ago, but as I look back I note that the answers missed my question.

A-A-AND, I'm still having trouble. Here's the problem:

When exporting from an MS Access Table or exporting query data based on the table to either MS Excel or MS Word, the phone and zip number formatting is erratic. All of the phones should export to the following format. (620) 555-5555. 5-digit zips should have only 5 digits with no dash; 55555 and not 55555-. Zip+4 zip codes should be of this format; 55555+4444. That is not what happens. Some of the records will actually have the right format in that field, but some do not. Many of the phone numbers FROM THE SAME FIELD come out as 6205555555 (no dashes), and that is terribly hard to read. The results are similar with zips; sometimes right and at other times incorrect. I sometimes get 555554444 with no dashes, (from the same field on different records), instead of 55555-4444.

I double checked an Excel table with data exported from Access query the other day. In the very same field, some was exported as numbers and had the correct formatting. Some was exported as TEXT (of all things) and had lost the formatting.

I could go the long-hand way and correct each record by hand after the export operation is completed, but with an active membership DB of nearly 300 records in a total DB of some 1700 records, that is totally impractical.

This actually seems to me to be like a programming glitch rather than something that has a logical work around.

JMack

[orange]

Post your database that has this issue, and tell us exactly how to reproduce the issue.

The "secret" to quality/consistent data is to do the validation (adjustments) during input.

[JMack]

Since much of the info in the Access DB is somewhat confidential, I'm sure you would understand that I want to upload it only to qualified MS Access personnel. At the same time I know that I need to give you as much info as necessary to achieve my needs. What are the privacy safeguards if I upload?
JMack

[ssanfu]

What are the privacy safeguards if I upload?

None...

Create a copy of the dB, delete most of the records. Leave a few records, but change sensitive data to made up info.