Format an Access report field for Social Security Number

I am using Access 2007. I have an input form that collect SS#'s with an input mask and saves it in a text field. The contents of the text field is 123456789 for example.

The report field format property but does not have any syntax examples.
I would guess it would be something like 999-99-9999 but that doesn't work.

What is the syntax to enter in the format property of the text box on a report to take 123456789 from the table and display it as 123-45-6789?

Thanks in advance for your help.
Bob
LVL 1
Robert WardlowPresidentAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
TracyConnect With a Mentor VBA DeveloperCommented:
See attached example.  The table has a text field with 9 digits in it.  The report displays the text field and applies the input mask.

Alternatively, you can apply the input mask directly to the table as well.

Database2.accdb
0
 
Rey Obrero (Capricorn1)Commented:
use a query as the record source of the report, in the query format the column like this

SSN:format([ss#],"000-00-0000")

0
 
TracyVBA DeveloperCommented:
In Design mode, right click on the text box in the form and click properties.  Then click on the Data tab and in the input mask section, click on the ellispes to the right.

Then choose Social Security Number as the format.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
TracyVBA DeveloperCommented:
Actually, for 2007, instead of right clicking on the text box and click properties, you would just click on the text box and click on the Property Sheet button in the Tools section.  See attached screenshot.

Screenshot.jpg
0
 
Helen FeddemaCommented:
This is a report, so an Input Mask wouldn't help.  Do the formatting in the record source query or SQL, as capricorn suggests.
0
 
TracyVBA DeveloperCommented:
You can use an input mask on the report as well, as long as the source field is a Text or Date.
0
 
Robert WardlowPresidentAuthor Commented:
Thanks to all of you for your help!

I knew I could do it with formatting in the query but I really wanted to know how to control it in the report.

I never thought to do it as an input mask on the data tab of the property box.

Thanks again
0
 
TracyVBA DeveloperCommented:
Happy to help :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.