By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 1,070 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

Deleting a "+" from a phone number in Access table...help!

P: 1
Hello all,

I am fairly new to Access and am running into a problem. I am looking at a table that has all phone numbers that have called our business for the month of November. However, many of our numbers that have automatically populated have populated like this in 2003 Access, and this an example of how the column appears:

Incoming Calling Number (Column header)

+12097774566
+12104380608
2104007083
+12106733242
2093212556
+12107788847

I have 1500 records, and I am trying to delete the "+"'s and sort the phone numbers A/Z. I have copied this into Excel and deleted the "+" by the find "+" and "replace with nothing" tool. While that deleted the "+", when I tried to sort A/Z I found that Excel sorted the # that were previously "+", and then sorted all the regular numbers separately.
I also tried to do the same in notepad and paste the column back in, but Access couldn't handle the paste and freezed up.

If I could have help with this, I would be very thankful. As stated above, I just started using Access. Please be very descriptive! :)
Dec 7 '07 #1
Share this Question
Share on Google+
2 Replies


Dököll
Expert 100+
P: 2,364
I have 1500 records, and I am trying to delete the "+"'s and sort the phone numbers A/Z. I have copied this into Excel and deleted the "+" by the find "+" and "replace with nothing" tool. While that deleted the "+", when I tried to sort A/Z I found that Excel sorted the # that were previously "+", and then sorted all As stated above, I just started using Access. Please be very descriptive! :)
Hey there, have a look here, see if this article saves you, I actually am going to try it for the dash in certain SSNs 000-00-0000. Not too savvy at Access either, I feel your pain. If I get it, I try to explain, not making any promises though so please try yourself ;-)

http://support.microsoft.com/?kbid=210433
Dec 7 '07 #2

Dököll
Expert 100+
P: 2,364
Hey there, have a look here, see if this article saves you, I actually am going to try it for the dash in certain SSNs 000-00-0000. Not too savvy at Access either, I feel your pain. If I get it, I try to explain, not making any promises though so please try yourself ;-)

http://support.microsoft.com/?kbid=210433
Just in case the link no longer works...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function FindTabs(WhichField As String) As String
  3.    Dim intCounter As Integer
  4.    Dim strText As String
  5.    Dim intStart As Integer
  6.  
  7.    intStart = 1
  8.    intCounter = 1
  9.    strText = WhichField
  10.  
  11.    Do Until intCounter = 0
  12.       ' Chr(9) is the Tab character.
  13.       ' Replace Chr(9) with the ANSI code for the character
  14.       ' you are searching for.
  15.       intCounter = InStr(intStart, strText, Chr(9))
  16.       intStart = intCounter + 1
  17.       If intCounter > 0 And Not IsNull(intCounter) Then
  18.          strText = ReplaceTabs(intCounter, strText)
  19.       End If
  20.    Loop
  21.  
  22.    FindTabs = strText
  23. End Function
  24.  
  25.  
Good luck to you!

Dököll
Dec 7 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.