Printing phone numbers in Access 2002 (2002)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 Len([PhoneNumber])
– 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:

"(" & Left([PhoneNumber],3) & ") " & Mid([PhoneNumber],4,3) & "-" & Right([PhoneNumber],4)

– Execute the query by clicking the Run button.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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:

• Make a copy of the database and work with that.
• Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
• In the remaining table(s), remove most records – leave only the minimum number necessary to demonstrate the problem.
• Remove or modify data of a confidential nature.
• Do a compact and repair (Tools/Database Utilities).
• Make a zip file containing the database; it should be below 100KB.
• If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it.
• Attach the zip file to a reply.
  [/list]That would allow Loungers to look at the problem directly.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.]

Reply | Quote

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.]

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Thank you. I see what I did wrong — I put the parameters under the Len([Phone]) column, while it really belonged in the Phone column. I actually copied your query into my original database (after testing it on the stripped down one) and it worked great — all my phone number are updated! Thank you so much!

Sheryl King

Reply | Quote

Thank you. I see what I did wrong — I put the parameters under the Len([Phone]) column, while it really belonged in the Phone column. I actually copied your query into my original database (after testing it on the stripped down one) and it worked great — all my phone number are updated! Thank you so much!

Sheryl King

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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:

• Make a copy of the database and work with that.
• Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
• In the remaining table(s), remove most records – leave only the minimum number necessary to demonstrate the problem.
• Remove or modify data of a confidential nature.
• Do a compact and repair (Tools/Database Utilities).
• Make a zip file containing the database; it should be below 100KB.
• If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it.
• Attach the zip file to a reply.
  [/list]That would allow Loungers to look at the problem directly.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 Len([PhoneNumber])
– 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:

"(" & Left([PhoneNumber],3) & ") " & Mid([PhoneNumber],4,3) & "-" & Right([PhoneNumber],4)

– Execute the query by clicking the Run button.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote