Query Conundrum - Adding Numbers to a Field

[databasedonr]

Greeting folks:

I have a query question:

In a field (text data) that is used for phone numbers. Most of the phone numbers are 10 digits, but I have several hundred that are 4 digits (representing extensions in a local exchange).

We are in the process of making our database accessible beyond our local exchange -- therefore, I need to change the all the four digit numbers to 10 digit numbers.

The good news is that I know exactly what the first 6 digits will be, and they will all be the same. The bad news is, I don't have a clue how I might do this.

So, for example, I might have these three records:

123-456-7890
6788
6789

And where I have the 6788 and 6789, I need to make these numbers look like:

321-123-6788
321-123-6789

Thanks in advance,

 

[dcx693]

You could either update the data in the table, or just dynamically "calculate" the phone numbers. Either way, it will be done through a query with a calculated field.

Create an expression like this in the field line of a select query where you have your phone number field, I'll assume it's called "Phone":
Full phone: IIf(Len([Phone])=4,"321123" & [Phone],[Phone])
This won't update the data in the table, it will only display the "321123" in front of records that have 4-digits.

If you then wish to update the data table. Change the select query into an update query, search for records that have only 4 digits then put a formula like "321123" & [Phone] on the update line and run it.

 

[databasedonr]

Thanks! dcx693, you have the answer!

That worked a treat. I was sniffing around the Len function, but hadn't used it properly -- and hadn't considered the IIF.

Very clever:

the actual update query I used was:

UPDATE tblPhoneData SET tblPhoneData.PhoneNumber = "321-123-" & [PhoneNumber]
WHERE (((Len([PhoneNumber]))=4));

with of course dummy numbers here to protect the guilty.

Thanks again.