77383

Reformatting a Phone Number in Access

Question:

I'm in the process of uploading data into an access database, and some of the data has phone numbers listed in the format of 9999999999 and others are listed as 999-999-9999. I would like for all the numbers to read 9999999999 so that way they can be cross referenced. I've been trying to run an update query on the numbers that are in the undesired format, but I am having no success. Blow is how I have the query set up:

Field: Wireless Number Table: Table to be updated Update to: Mid([Table].[Wireless Number],2,3)+Mid([Table].[Wireless Number],7,3)+Right([Table].[Wireless Number],4) Criteria: Not Like "**********"

Obviously, I'm not sure how to go about this, so that set up is a shot in the dark. What is the best way to go about getting the format that I need ?

Answer1:

This SQL function should work, <strong>replace [wireless num] with your field name</strong>:

test: IIf(Mid([wireless num],4,1)="-",Left([wireless num],3) & Mid([wireless num],5,3) & Right([wireless num],4),[Wireless Num])

If the 4th character is a -, that means it is undesired. In that scenario, it extracts each section of the phone number as substrings, concatenates them, and displays them as one string.

If the 4th character is not a -, we can assume the phone number is in the desired format.

<hr />

This should also work:

Replace([wireless num],"-","")

It searches the Wireless Num field for -, and replaces it with a zero length string.

Recommend

  • How do configure optional or “extraneous” URLs?
  • How to know if MAC address is attached to wireless card or ethernet card?
  • Get information from JSON
  • How to resolve promises one after another?
  • Recursive quick-sort causing segmentation fault (not overflow)
  • iOS: how to respond to up/down/left/right arrow event from physical keyboard?
  • Stop Outlook from converting HTML to RTF
  • Dilemma in deciding how to create CSS for H1, H2, H3 etc
  • Rotation gesture produces undesired rotation
  • Can Delphi XE4 support iOS 4.x?
  • can I trap exit(self(), kill)?
  • Retina 4 2x is never used for iPhone 5 simulator in Xcode 6.2 image asset
  • strptime in Javascript
  • Substitute partial line in vim using only substitute command
  • Eliminate units when using quantities for Python
  • How to add multiple columns in Apache Spark
  • how to sort the field in the mongo document which is inside array
  • sorting three number in a specific order
  • How to Count the Number of a Specific Character in a Cell with Excel VBA
  • Swift: Validate Username Input
  • Nhibernate QueryOver Orderby
  • Oracle: Using CTE with update clause
  • SQL - count occurrences of gender
  • unable to get jsonEncode in magento2
  • nonblocking BIO_do_connect blocked when there is no internet connected
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Get data from AJAX - How to
  • Retrieving value from sql ExecuteScalar()
  • Bug in WPF DataGrid
  • Display Images one by one with next and previous functionality
  • Run Powershell script from inside other Powershell script with dynamic redirection to file
  • SQL merge duplicate rows and join values that are different
  • KeystoneJS: Relationships in Admin UI not updating
  • How to set the response of a form post action to a iframe source?
  • Hits per day in Google Big Query
  • Change div Background jquery
  • Qt: Run a script BEFORE make
  • Checking variable from a different class in C#
  • reshape alternating columns in less time and using less memory