Results 1 to 2 of 2
2001-10-11, 23:32 #1cricket7Guest
Update query with phone numbers (2000/97)
<img src=/S/hello.gif border=0 alt=hello width=25 height=29> I am trying to help a friend with his database which is 97 and mine is 2000. He has a database for our police dept with 36,974 records. He has some phone numbers with 7,10, 11,12 digits. The numbers that have area codes could be one of about 10 different area codes. He wants to change the numbers to all have 12 digits. They need to be updated to 8-1-999-999-9999. By January we all have to use 11 digits to make all phone calls. Here at work we have to add an 8 to dial the number. He has the numbers in the format without dashes. The computer the database is in is a special computer that dials the numbers automatically if it is necessary. How can I make an update query to update the records to be 81??????????. Can I also switch the format to use dashes but not interfere with the computers ability to phone the numbers. I know that in the beginning if you use the input mask, you can determine whether you want the numbers to be stored with or without the dashes. But what about now after he set up the database without any formatting. Sorry this is so long and thanks very much for your help. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> <img src=/S/help.gif border=0 alt=help width=23 height=15> <img src=/S/help.gif border=0 alt=help width=23 height=15>
2001-10-12, 13:27 #2
- Join Date
- Jun 2001
- Crystal Beach, FL, Florida, USA
- Thanked 37 Times in 36 Posts
Re: Update query with phone numbers (2000/97)
First of all, do you really need to preface all stored entries with 8-1? I would think it would be better to add these digits to your entry at the time you were making the call. They could be part of the formatting.
If you want to use update queries, then I would do it in several passes; one for each circumstance. I'd use Len(phone) as selection criteria, so the first pass might be if it =7. Assuming area code is 999, your "Update to" would be "999" & [Phone]. Subsequent update queries would deal with each of the other possibilities for Len(phone).