Solved

Format an Access report field for Social Security Number

Posted on 2010-08-25
8
2,768 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:Rwardlow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33521333
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
 
LVL 24

Expert Comment

by:broomee9
ID: 33521400
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
 
LVL 24

Expert Comment

by:broomee9
ID: 33521430
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 33521444
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
 
LVL 24

Expert Comment

by:broomee9
ID: 33521521
You can use an input mask on the report as well, as long as the source field is a Text or Date.
0
 
LVL 24

Accepted Solution

by:
broomee9 earned 500 total points
ID: 33521555
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
 
LVL 1

Author Closing Comment

by:Rwardlow
ID: 33522145
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
 
LVL 24

Expert Comment

by:broomee9
ID: 33522225
Happy to help :-)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question