Solved Copying telephone numbes

[Eljefegeneo]

I have been trying to figure out a way to copy a telephone number that is usually in the format (800)-555-1212 into a form. The form is formatted for telephone numbers so the input mask is (???)-???-????. Right now when I copy the number to the telephone control it only pastes the area code. While the above is the usual format for a telephone number that I am copying, there are other formats like ???.???.???? and ???-???-????. Any suggestions on how I might do this? Thanks.

 

[Uncle Gizmo]

Well I'd start off with Mintys code in this thread here:-

Code to replace special character with space

Hi all, We have a spreadsheet that i upload onto a table, i use on of the fields to build a file path but if there is a special character it causes problems, i need to replace the below with spaces if they are in my field called path • < (less than) • > (greater than) • : (colon) • "...

www.access-programmers.co.uk

You can use it to specify which characters you want to strip from the string and just leave yourself with just numbers.

I suspect Mintys code would also remove any spaces, but if not, there are plenty of routines on Access World Forums (AWF) for removing spaces.

I used Minty's code to remove illegal characters from the names of Excel sheets I created from MS Access. I blogged about it here:-

Make Excel Sheets From Access Table - Nifty Access

Excel Sheets From Access Table If you've ever faced the challenge of exporting data from Microsoft Access into Excel, you’re not alone! One of the most common requests is to organize data by categories, like exporting car makes into separate Excel sheets or even separate Excel files. I’ve...

www.niftyaccess.com

 

[Isaac]

I have been trying to figure out a way to copy a telephone number that is usually in the format (800)-555-1212 into a form. The form is formatted for telephone numbers so the input mask is (???)-???-????. Right now when I copy the number to the telephone control it only pastes the area code. While the above is the usual format for a telephone number that I am copying, there are other formats like ???.???.???? and ???-???-????. Any suggestions on how I might do this? Thanks.

Normally in Access (especially), and even (preferably) in Excel, we don't copy and paste. Anything. We write code to set a value equal to something.

I assume you have some code of some kind - you should post it or else we're just guessing

 

[Eljefegeneo]

No, I am getting a number from a web page and copying it, then pasting it to the forms telephone number field. I was hoping that there would be a simple anser for this, but perhaps I need to have an unbound field, to which I copy the formatted telephone number then when I do something like double click it, strips the unwanted characters and then I can copy and pasted it to the telephone field. If it is only numbers say 8005551212 it will copy perfectly.

 

[Uncle Gizmo]

No, I am getting a number from a web page and copying it, then pasting it to the forms telephone number field. I was hoping that there would be a simple anser for this, but perhaps I need to have an unbound field, to which I copy the formatted telephone number then when I do something like double click it, strips the unwanted characters and then I can copy and pasted it to the telephone field. If it is only numbers say 8005551212 it will copy perfectly.

You are on the right track with that.

Your first step is to create a small sample database with a single table called "tblTel" to hold the telephone numbers.

Create a form based on "tblTel" which will have a field displayed in it where you can enter the telephone number into the telephone field in "tblTel"

Add an unbound text box.

Add a command button.

To test that a command button is working right, add the code to have the command button open a message box saying 'hello" .

Now change the command button code so that it takes text from the unbound textbox and shows it in the message box.

Change the command button code so that it takes the text of the unbound textbox and places it in the bound text box.

Look at the code I posted a link to earlier, the stripper function. Copy it and paste it into your form module next to the command button code.

Change the command button code yet again so that it Calls this function and also passes the unbound text box contents into the stripper function as a parameter and returns the result into the bound text box.

Everytime any of these steps fail, and it will, post here for help and I am sure someone will point you in the right direction.

 

[Uncle Gizmo]

I've done a YouTube video demonstrating how to use the steps I have suggested above to answer this question...

How to Answer your own Question - Nifty Access​

 

[Eljefegeneo]

Update: Using the function found in a previous post, fStripIllegal, which I modified to include some additional characters, I went through my usual trial and error method of solving problems and came up with the following function. First I added an unbound control to the form and called it txtTel. Now when I paste the telephone number into this control it automatically adds the corrected formatted telephone number to my Telephone field which has an input mask. The function is in the OnChange() event of the unbound control txtTel.

Function UpdateTel()
If Me.Dirty Then
Me.Dirty = False
End If

Dim NewTel As String
  NewTel = txtTel
     fStripIllegal NewTel
         Me.txtTel = Trim(NewTel)
             txtTel = Replace(txtTel, " ", "")
                  Me.Telephone = Format(Me.txtTel, "(###) ###-####")
End Function

I had to add the Tim and Replace features to delete any spaces and then I had to reformat the string of numbers so that it pasted correctly.
Thanks to everyone who helped on this. Much appreciated.

 

[Gasman]

Isn't that then storing the format?, when I would have thought you just want the numbers and apply thr format when showing them?

 

[Eljefegeneo]

I don't know if I can answer that question. The Telephone field is a text field with the input mask of !\(999") "000\-0000;0;_
When pasting the text (number) into the telephone field it is only a continuous string. The control on the form shows it to be (XXX)-XXX-XXXX.

In any case, my question is solved to my satisfaction, which I have been told in many previous posts, if you like it and it works, you have solved your problem. May not be the best or easiest resolution, but it is solved. Thanks.

My money is on Tampa Bay. got my $1 in the other day.