Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    San Jose, CA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    phone number input filter (2002 SP2)

    I'm sure this is a dumb question but I'm having trouble getting my input filter for phone numbers to work the way the books and help file says they are supposed to. I use the standard filter "!(999") "000-0000;;_" described in several books and the online help but it still does not seem to want to fill the field from right to left (allowing the area code to be optional). The field size is 10 and data type is text. The first numbers I type go into the area code, thus I'm forced to enter all 10 digits. Can someone point out what I'm missing?

    Also, a bonus question. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> What I would really like to do is have the area code defaulted since 98% of the data to be entered will be the same area code but setting the default value in the table properties does not seem to work for me either.

    Thanks for your help.
    Don

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: phone number input filter (2002 SP2)

    In this case, it is probably easier to use separate fields for are code (3 digits, set default value to the area code that covers 98% of your records) and for local number (7 digits). You can concatenate them into the full telephone number in a query by creating a calculated field
    Telephone: "(" & [AreaCode] & ") " & [LocalNumber]

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: phone number input filter (2002 SP2)

    Using a separate field for area code is a good idea anyhow because area codes get changed sometimes. It's easier to update a separate field than to try to test for the length of the number before updating the first 3 digits, especially if any of your phone numbers were imported from a file or got entered directly instead of through a form. In that case, you won't be able to rely on the input mask.
    Charlotte

  4. #4
    New Lounger
    Join Date
    Jan 2001
    Location
    San Jose, CA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: phone number input filter (2002 SP2)

    Thanks for the advice. I agree (especially in trying to move toward more normalized forms) that separating the area code makes sense. Since I'm still new to all this, I still need to figure out if a separate table for area code makes sense. You see, I currently have 7 phone numbers in the current table scheme (which, is probably a bad design to begin with) but the data is unique (parents and emergency contact information for a student) so it's not clear to me how much I should decompose the table.

    Did I mention that this is my first database design? <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  5. #5
    New Lounger
    Join Date
    Jan 2001
    Location
    San Jose, CA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: phone number input filter (2002 SP2)

    One more thing. My original request was why the "!" in the mask did not fill in the field from right to left. Does anyone know why this is not working? Is it a problem if I have the input mask defined for the table as well as the input form?

    Don

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: phone number input filter (2002 SP2)

    I can't find anything about this, but ! in an input mask has absolutely no influence whatsoever in my Access 2002 SP-2. It doesn't matter what kind of field or input mask I use, ! does nothing... <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: phone number input filter (2002 SP2)

    What do you mean by "I have 7 phone numbers"? That each record now contains seven different phone numbers? If so, you could split them into 14 fields (an area code and phone number without area code for each of the 7; you can't assume that the seven numbers will have the same area code, or am I mistaken?)

    An alternative would be to have a separate table with phone numbers; this table would have 4 fields:
    - An ID field to link a record to the main table
    - Area code
    - Phone number without area code
    - Type (home phone, work phone, cell phone, ...)
    In this setup, you have a one-to-many relationship between records in the main table and records in the phone numbers table. For data entry and editing, you would create a main form with a phone numbers subform.

    But I recommend sticking with 14 fields in the main table; it is easier to search for a phone number.

    You could have a lookup table for area codes if you don't want the users to type the area codes themselves; this lookup table would be linked to each of the area code fields in the main table.

  8. #8
    New Lounger
    Join Date
    Jan 2001
    Location
    San Jose, CA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: phone number input filter (2002 SP2)

    I'm reading 4 different books on Access and they all talk about this. Directly from the Access help file it states:

    " ! Causes the input mask to display from right to left, rather than from left to right. Characters typed into the mask always fill it from left to right. You can include the exclamation point anywhere in the input mask. "

    "But in one of the books I'm reading "Step by Step Microsoft Access 2002" it states on page 143. "The exclamation point causes Access to fill the mask from left to right with whatever is typed. The parentheses and hyphen are characters that Access will insert in the specified places. The 9s represent optional digits, . . ."

    This is describing the Input Mask Wizard's phone mask !(999") "000-0000;;_

    I guess I have not been reading this carefully enough. They do state that the "characters typed . . fill it from left to right" but what good does having the *mask* displayed from right to left when the chacters get filled from left to right? Talk about confusing. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  9. #9
    New Lounger
    Join Date
    Jan 2001
    Location
    San Jose, CA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: phone number input filter (2002 SP2)

    Oops! I posted before seeing your latest post.

    Yup, I've been thinking about the separate table for phone numbers too but I tend to agree that just having the separate fields for area codes might be easier in my case. Being new to database design I want the simplest approach. The database will not exceed 300 students so we are not talking about a need for high level of normalization here.

    Thanks for all your help and ideas on this.
    Don

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: phone number input filter (2002 SP2)

    "We'll never need..." are famous last words when building a database. If the thing is successful, the next thing they'll ask for is what you thought would never be needed! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> Building design "right" on a small scale project gets you into the habit and teaches you a lot about what works and what doesn't.
    Charlotte

Posting Permissions

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