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 ?

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.