Member Avatar for 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

  • 2 Contributors
  • 2 Replies
  • 100 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by mel01
Member Avatar for 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])

Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.