Export Query from Access 2003 - inconsistent results

[mel01]

Hello,

My first post and I am hoping someone can resolve this strange dilemma.

I am creating a text file by exporting the results of a query. One of the fields is a phone number that is formatted 999.999.9999

Unfortunately not all exported phone numbers are correctly formatted in the resulting file. Some lose the periods and look like this: 9999999999. Others however are formatted correctly.

All of the phone numbers are formatted correctly in the query prior to being exported.

Thanks in advance for any assistance with the dilemma.

Cheers,
mel

[timothybard]

The query may be displaying the values that are numeric with formatting while the values with the periods are being shown as they are. This results in the values looking in the same format when they are actually storing different data. My advice is to do the following:

1) In the query, add a field to count the size of the values. i.e LenghtOfField:Len([field])

If you see some records with a result of 10 and others with the result of 12, then you need to add a condition to add the periods to the records that have a values of length 10. i.e. result:IIF(len([field]) = 10 , left([field],3) & "." & mid([field],4,3) & right([field],4) , [field])

[mel01]

HI Timothy,

Thanks for your response and solution. IT greatly assisted me in resolving my dilemma as well as alerted me to some other problems.

Cheers,
mel