Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    La Jolla, California, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Printing phone numbers in Access 2002 (2002)

    I have a fairly basic database, but I used an input mask for phone numbers so I wouldn't have to keep entering the parentheses and hyphens. That works great. But when I go to print a report, the phone number prints as a 10-digit number. How can I print the phone number as (xxx) xxx-xxxx?

    Sheryl King
    San Diego

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Printing phone numbers in Access 2002 (2002)

    You can the use same input mask on the report as you use for data entry.

    Even though you don't input data to report, they can be used there for formatting.

    Look at the report in design view, and each phone number control will have an input mask property.
    Regards
    John



  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Printing phone numbers in Access 2002 (2002)

    An alternative to what John suggests is to actually store the Parentheses and Hyphens in the field - that is an option in the setting up of the input mask. Of course that means the field cannot be numeric, but must be text.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    La Jolla, California, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing phone numbers in Access 2002 (2002)

    Great! I do a lot of different reports based on this database, so I'd rather store the characters in the database. But I tried it and it appears that it can't be applied retroactively. New entries work fine, but the old entries still show 10 digits. I can go in and cut and paste back in all the numbers, but that seems pretty tedious. Any quick way to get the change to apply retroactively?

    Sheryl King
    San Diego

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

    Re: Printing phone numbers in Access 2002 (2002)

    You can use an update query for this:
    - Create a query based on your table.
    - Add the phone number field; for illustration purposes, I'll call it PhoneNumber. Use the real name in your query.
    - Add a calculated field <code>Len([PhoneNumber])</code>
    - Clear the Show check box and set the criteria for the calculated field to 10. This will restrict the query to 10 digit numbers only.
    - Select Query | Update Query to change the query to an update query.
    - In the Update To line under the PhoneNumber field, enter:

    <code>"(" & Left([PhoneNumber],3) & ") " & Mid([PhoneNumber],4,3) & "-" & Right([PhoneNumber],4)</code>

    - Execute the query by clicking the Run button.

  6. #6
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    La Jolla, California, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing phone numbers in Access 2002 (2002)

    Help! I tried that, but when I tried to run it, I got an error message
    'Len([Phone])' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

    Sheryl King
    San Diego

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

    Re: Printing phone numbers in Access 2002 (2002)

    1. Are you absolutely sure that Phone is the name of the field?
    2. Activate the Visual Basic Editor (Alt+F11), then select Tools | References... If you see any references starting with MISSING, clear their check box, then click OK. Then switch back to Access, and try to run the query again.

  8. #8
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    La Jolla, California, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing phone numbers in Access 2002 (2002)

    When I look at the table in design view, that is the name in the left-hand column. I assume that is the field name. Could it be something else?

    There are no references starting with MISSING.

    Sheryl King

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

    Re: Printing phone numbers in Access 2002 (2002)

    Len is a standard function that can be used in queries, it shouldn't cause problems.

    Oh wait - do you have a field named Len in your table, or do you have a VBA function called Len in your database?

    If not, I don't know how to solve this without seeing the database. Perhaps you can post a stripped down version of the database:
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it.
    <LI>Attach the zip file to a reply.[/list]That would allow Loungers to look at the problem directly.

  10. #10
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    La Jolla, California, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing phone numbers in Access 2002 (2002)

    After fiddling a bit, yes, I'm sure that is the field name.

    Do I have to make Len([Phone]) a field in the table or just in the query. I was just using it in the query.

    Sheryl King

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

    Re: Printing phone numbers in Access 2002 (2002)

    Len([Phone]) is to be a calculated field in the query. Please see the reply I posted in the meantime.

  12. #12
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    La Jolla, California, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing phone numbers in Access 2002 (2002)

    I have a field entitled Length(inches). I suppose that's causing the problem. If I rename that field will I lose all the data currently in it?

    Sheryl King

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

    Re: Printing phone numbers in Access 2002 (2002)

    I don't think that field would cause a conflict with Len. Renaming the field wouldn't cause data to be lost, but you would have to adapt all queries, forms and reports using this field, so I wouldn't do it if I were you.

    [Personally, I never use spaces, parentheses, etc. in field names, but that has nothing to do with the current problem.]

  14. #14
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    La Jolla, California, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing phone numbers in Access 2002 (2002)

    Here is a stripped down version of the data base per your very clear instructions. I hope you or someone else can figure out what I'm doing wrong!

    Sheryl King
    Attached Files Attached Files

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

    Re: Printing phone numbers in Access 2002 (2002)

    Does the problem still occur for you in the version you posted? It doesn't for me. I created the update query, and it works OK. The report shows the phone numbers correctly formatted.

    I have attached the database with the update query, but with the original data. See if you can run the query.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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