Results 1 to 2 of 2
  1. #1

    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>

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Crystal Beach, FL, Florida, USA
    Thanked 41 Times in 40 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).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts