Making "-" in Social Security No stay in Field

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Greetings all,
I need to convert a field that has social security numbers stored as
XXXXXXXXX to XX-XXX-XXXX. When I use the Input mask, the "-" are
present when the table is displayed, however, when I use this table in
creating a report using Crystal Reports, my string is back to
XXXXXXXX.
I can manually put the "-" in the fields, and it works, but I was
hoping for a less labor intensive way.
Thanks,
Jim
 
Assuming that you're using a query as the basis of the report, add a
calculated field to the query that converts the stored SSN to one with
dashes:

DashSSN: Left([SSN], 2) & "-" & Mid([SSN], 3, 3) & "-" & Right([SSN], 4)

Then use the DashSSN field in your report where you wish to display the SSN.
 
Say Ken,
Do they do things differently in your part of the country?
DashSSN: Left([SSN], 2) & "-" & Mid([SSN], 3, 3) & "-" & Right([SSN], 4)
Click to expand...
<

DashSSN: Left([SSN], 3) & "-" & Mid([SSN], 4, 2) & "-" & Right([SSN], 4)
works better in here in the West. <g>


Jim,
To convert all the existing SSN's to include the hyphens, run an update
query.
After you update your table, then change your Input Mask so that
future entries will be saved with the mask.
Use:
000\-00\-0000;0;_
as the input mask. See Access Help.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Ken Snell said:
Assuming that you're using a query as the basis of the report, add a
calculated field to the query that converts the stored SSN to one with
dashes:

DashSSN: Left([SSN], 2) & "-" & Mid([SSN], 3, 3) & "-" & Right([SSN], 4)

Then use the DashSSN field in your report where you wish to display the SSN.

--
Ken Snell
<MS ACCESS MVP>

Jim said:
Greetings all,
I need to convert a field that has social security numbers stored as
XXXXXXXXX to XX-XXX-XXXX. When I use the Input mask, the "-" are
present when the table is displayed, however, when I use this table in
creating a report using Crystal Reports, my string is back to
XXXXXXXX.
I can manually put the "-" in the fields, and it works, but I was
hoping for a less labor intensive way.
Thanks,
Jim
Click to expand...
Click to expand...
 
Guess we do do the
same here in Midwest, as I think we have the same "government"!
Click to expand...

Nah! Here in California we have no government, only side show
carnival stuff!!! :-(

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Ken Snell said:
Fredg said:
Say Ken,
Do they do things differently in your part of the country?
DashSSN: Left([SSN], 2) & "-" & Mid([SSN], 3, 3) & "-" & Right([SSN],
Click to expand...
4)
<

DashSSN: Left([SSN], 3) & "-" & Mid([SSN], 4, 2) & "-" & Right([SSN], 4)
works better in here in the West. <g>
Click to expand...

< g > That's what I get for literally reading the post! Guess we do do the
same here in Midwest, as I think we have the same "government"!
Click to expand...
 
Ken and Fred,
Thanks so much for your quick and courteous replies.
I'll try both of your suggestions.
I should have been a little clearer, I don't want to change the
original table, but I need to run a query that after selecting my
records, then changes the records in the query to SSNs with dashes.
Jim

Fredg said:
Say Ken,
Do they do things differently in your part of the country?
DashSSN: Left([SSN], 2) & "-" & Mid([SSN], 3, 3) & "-" & Right([SSN], 4)
Click to expand...
<

DashSSN: Left([SSN], 3) & "-" & Mid([SSN], 4, 2) & "-" & Right([SSN], 4)
works better in here in the West. <g>


Jim,
To convert all the existing SSN's to include the hyphens, run an update
query.
After you update your table, then change your Input Mask so that
future entries will be saved with the mask.
Use:
000\-00\-0000;0;_
as the input mask. See Access Help.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Ken Snell said:
Assuming that you're using a query as the basis of the report, add a
calculated field to the query that converts the stored SSN to one with
dashes:

DashSSN: Left([SSN], 2) & "-" & Mid([SSN], 3, 3) & "-" & Right([SSN], 4)

Then use the DashSSN field in your report where you wish to display the SSN.

--
Ken Snell
<MS ACCESS MVP>

Jim said:
Greetings all,
I need to convert a field that has social security numbers stored as
XXXXXXXXX to XX-XXX-XXXX. When I use the Input mask, the "-" are
present when the table is displayed, however, when I use this table in
creating a report using Crystal Reports, my string is back to
XXXXXXXX.
I can manually put the "-" in the fields, and it works, but I was
hoping for a less labor intensive way.
Thanks,
Jim
Click to expand...
Click to expand...
Click to expand...
 
Jim,
That's fine.
Just use the expression in a Select Query.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Jim said:
Ken and Fred,
Thanks so much for your quick and courteous replies.
I'll try both of your suggestions.
I should have been a little clearer, I don't want to change the
original table, but I need to run a query that after selecting my
records, then changes the records in the query to SSNs with dashes.
Jim

"Fredg" <[email protected]> wrote in message
Say Ken,
Do they do things differently in your part of the country?
DashSSN: Left([SSN], 2) & "-" & Mid([SSN], 3, 3) & "-" & Right([SSN],
Click to expand...
4)
<

DashSSN: Left([SSN], 3) & "-" & Mid([SSN], 4, 2) & "-" & Right([SSN], 4)
works better in here in the West. <g>


Jim,
To convert all the existing SSN's to include the hyphens, run an update
query.
After you update your table, then change your Input Mask so that
future entries will be saved with the mask.
Use:
000\-00\-0000;0;_
as the input mask. See Access Help.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Ken Snell said:
Assuming that you're using a query as the basis of the report, add a
calculated field to the query that converts the stored SSN to one with
dashes:

DashSSN: Left([SSN], 2) & "-" & Mid([SSN], 3, 3) & "-" & Right([SSN], 4)

Then use the DashSSN field in your report where you wish to display
Click to expand...
the
SSN.
--
Ken Snell
<MS ACCESS MVP>

Greetings all,
I need to convert a field that has social security numbers stored as
XXXXXXXXX to XX-XXX-XXXX. When I use the Input mask, the "-" are
present when the table is displayed, however, when I use this table in
creating a report using Crystal Reports, my string is back to
XXXXXXXX.
I can manually put the "-" in the fields, and it works, but I was
hoping for a less labor intensive way.
Thanks,
Jim
Click to expand...
Click to expand...
Click to expand...
 
Fredg said:
Jim,
That's fine.
Just use the expression in a Select Query.
Click to expand...

Fred and Ken,
I'm sorry to be such a bother, I'm not a total Access novice, but with
this one I'm over my head. Here's what I've done:

1. Create a query that selects all of my records from the table with
fields, SSN, and PAT_ID
2. My SQL statement looks like this
SELECT CDS.SSN, CDS.PAT_ID
FROM CDS;

I'm not sure where I should put the expression-- Left([SSN], 3) & "-"
& Mid([SSN], 4, 2) & "-" & Right([SSN], 4)--in the SQL statement.

Also, can I put this expression in the Criteria line of the SSN field
in the query? I tried Ken's update query suggestion, but the query
kept asking me for "Field to Update to."

I hate asking for such hand holding,
TIA,
Jim








DashSSN: Left([SSN], 3) & "-" & Mid([SSN], 4, 2) & "-" & Right([SSN],
4)
works better in here in the West. <g>


Jim,
To convert all the existing SSN's to include the hyphens, run an update
query.
After you update your table, then change your Input Mask so that
future entries will be saved with the mask.
Use:
000\-00\-0000;0;_
as the input mask. See Access Help.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Assuming that you're using a query as the basis of the report, add a
calculated field to the query that converts the stored SSN to one with
dashes:

DashSSN: Left([SSN], 2) & "-" & Mid([SSN], 3, 3) & "-" & Right([SSN], 4)

Then use the DashSSN field in your report where you wish to display
Click to expand...
the
SSN.
--
Ken Snell
<MS ACCESS MVP>

Greetings all,
I need to convert a field that has social security numbers stored as
XXXXXXXXX to XX-XXX-XXXX. When I use the Input mask, the "-" are
present when the table is displayed, however, when I use this table in
creating a report using Crystal Reports, my string is back to
XXXXXXXX.
I can manually put the "-" in the fields, and it works, but I was
hoping for a less labor intensive way.
Thanks,
Jim
Click to expand...
Click to expand...
Click to expand...
 
Jim,
This is a Select query, not an Update query, correct?

If you are using the QBE grid, you already have a SSN and a Pat_ID column.
In the next empty column, on the top Field line, write:
DashSSN:Left([SSN], 3) & "-" & Mid([SSN], 4, 2) & "-" & Right([SSN], 4)

I would also suggest you write:
Is Not Null
in the SSN criteria line to avoid an #Error if that SSN field is null.

When done, click on the View Tool button and select SQL view.
The query should read like this:

SELECT CDS.SSN, CDS.PAT_ID, Left([SSN], 3) & "-" & Mid([SSN], 4, 2) & "-" &
Right([SSN], 4) as DashSSN
FROM CDS
Where SSN is not null;

Click the Bang (!) tool button to run the query.

SSN Pat_ID DashSSN
123456789 101 123-45-6789


Note: You do not need to have the SSN field column in the query
unless you just want to compare the changes made.

SELECT CDS.PAT_ID, Left([SSN], 3) & "-" & Mid([SSN], 4, 2) & "-" &
Right([SSN], 4) as DashSSN
FROM CDS
Where SSN is not null;

Will work just as well.
Pat_ID DashSSN
101 123-45-6789

I hope you've got it now.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Jim said:
"Fredg" <[email protected]> wrote in message
Jim,
That's fine.
Just use the expression in a Select Query.
Click to expand...

Fred and Ken,
I'm sorry to be such a bother, I'm not a total Access novice, but with
this one I'm over my head. Here's what I've done:

1. Create a query that selects all of my records from the table with
fields, SSN, and PAT_ID
2. My SQL statement looks like this
SELECT CDS.SSN, CDS.PAT_ID
FROM CDS;

I'm not sure where I should put the expression-- Left([SSN], 3) & "-"
& Mid([SSN], 4, 2) & "-" & Right([SSN], 4)--in the SQL statement.

Also, can I put this expression in the Criteria line of the SSN field
in the query? I tried Ken's update query suggestion, but the query
kept asking me for "Field to Update to."

I hate asking for such hand holding,
TIA,
Jim








DashSSN: Left([SSN], 3) & "-" & Mid([SSN], 4, 2) & "-" & Right([SSN],
4)
works better in here in the West. <g>


Jim,
To convert all the existing SSN's to include the hyphens, run an update
query.
After you update your table, then change your Input Mask so that
future entries will be saved with the mask.
Use:
000\-00\-0000;0;_
as the input mask. See Access Help.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Assuming that you're using a query as the basis of the report, add a
calculated field to the query that converts the stored SSN to one with
dashes:

DashSSN: Left([SSN], 2) & "-" & Mid([SSN], 3, 3) & "-" &
Click to expand...
Right([SSN],
4)
Then use the DashSSN field in your report where you wish to
Click to expand...
display
the
SSN.
--
Ken Snell
<MS ACCESS MVP>

Greetings all,
I need to convert a field that has social security numbers stored as
XXXXXXXXX to XX-XXX-XXXX. When I use the Input mask, the "-" are
present when the table is displayed, however, when I use this
Click to expand...
table
in
creating a report using Crystal Reports, my string is back to
XXXXXXXX.
I can manually put the "-" in the fields, and it works, but I was
hoping for a less labor intensive way.
Thanks,
Jim
Click to expand...
Click to expand...
Click to expand...
 
Fred,
Thanks so much for this. This works quite well. I've learned a whole
bunch in the past week thanks to you and Ken. May I trouble you with
one more question?

Can I take what I've learned and generate a 2nd table, one with the
dashed-SSNs. The only reason I ask, is the query I've created as per
your instructions, when I try to create a 2nd query using this one and
then a table from my ODBC DB, I get either both fields blank (Dash SSN
& PATID)on an equal join, or Dash SSN but blank PATID, with a
left-outer join. A right outer join gives me all the records in the
ODBC table and DashSSN blank.

Any thoughts, and again thanks for all your help.
Jim


Fredg said:
Jim,
This is a Select query, not an Update query, correct?

If you are using the QBE grid, you already have a SSN and a Pat_ID column.
In the next empty column, on the top Field line, write:
DashSSN:Left([SSN], 3) & "-" & Mid([SSN], 4, 2) & "-" & Right([SSN], 4)

I would also suggest you write:
Is Not Null
in the SSN criteria line to avoid an #Error if that SSN field is null.

When
Click to expand...
done, click on the View Tool button and select SQL view.
The query should read like this:

SELECT CDS.SSN, CDS.PAT_ID, Left([SSN], 3) & "-" & Mid([SSN], 4, 2) & "-" &
Right([SSN], 4) as DashSSN
FROM CDS
Where SSN is not null;

Click the Bang (!) tool button to run the query.

SSN Pat_ID DashSSN
123456789 101 123-45-6789


Note: You do not need to have the SSN field column in the query
unless you just want to compare the changes made.

SELECT CDS.PAT_ID, Left([SSN], 3) & "-" & Mid([SSN], 4, 2) & "-" &
Right([SSN], 4) as DashSSN
FROM CDS
Where SSN is not null;

Will work just as well.
Pat_ID DashSSN
101 123-45-6789

I hope you've got it now.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Jim said:
"Fredg" <[email protected]> wrote in message
Jim,
That's fine.
Just use the expression in a Select Query.
Click to expand...

Fred and Ken,
I'm sorry to be such a bother, I'm not a total Access novice, but with
this one I'm over my head. Here's what I've done:

1. Create a query that selects all of my records from the table with
fields, SSN, and PAT_ID
2. My SQL statement looks like this
SELECT CDS.SSN, CDS.PAT_ID
FROM CDS;

I'm not sure where I should put the expression-- Left([SSN], 3) & "-"
& Mid([SSN], 4, 2) & "-" & Right([SSN], 4)--in the SQL statement.

Also, can I put this expression in the Criteria line of the SSN field
in the query? I tried Ken's update query suggestion, but the query
kept asking me for "Field to Update to."

I hate asking for such hand holding,
TIA,
Jim








DashSSN: Left([SSN], 3) & "-" & Mid([SSN], 4, 2) & "-" & Right([SSN],
4)
works better in here in the West. <g>


Jim,
To convert all the existing SSN's to include the hyphens, run an update
query.
After you update your table, then change your Input Mask so that
future entries will be saved with the mask.
Use:
000\-00\-0000;0;_
as the input mask. See Access Help.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Assuming that you're using a query as the basis of the report, add a
calculated field to the query that converts the stored SSN to one with
dashes:

DashSSN: Left([SSN], 2) & "-" & Mid([SSN], 3, 3) & "-" &
Click to expand...
Right([SSN],
4)
Then use the DashSSN field in your report where you wish to display
the
SSN.

--
Ken Snell
<MS ACCESS MVP>

Greetings all,
I need to convert a field that has social security numbers stored as
XXXXXXXXX to XX-XXX-XXXX. When I use the Input mask, the "-" are
present when the table is displayed, however, when I use this
Click to expand...
table
in
creating a report using Crystal Reports, my string is back to
XXXXXXXX.
I can manually put the "-" in the fields, and it works, but I was
hoping for a less labor intensive way.
Thanks,
Jim
Click to expand...
Click to expand...
Click to expand...
 
Fred,
Updating the table would be best for sure, but I can't get the darn
thing to update, but I'm still trying.

I can see that I need to order a new book on Access. The one I have
simply doesn't get it. I have the developer's handbook, but it isn't
too handy for these types of problems. I believe SQL for Mere Mortals
is next.

Thanks so much for your help on this. Again, because of the input of
the newsgroup, I've really learned a great deal. I hope I can
contribute in the same way in the future.

Jim


Fredg said:
Jim,

I still don't understand why you don't just update the SSN field to
incorporate the dashes, but if you wish a new table, here is the SQL, using
the original non-hyphenated data.

SELECT tblBasicData.LastName, Left([SSN],3) & "-" & Mid([SSN],4,2) & "-" &
Right([SSN],4) AS NewSSN INTO tblMadeThisTable
FROM tblBasicData
WHERE (((tblBasicData.SSN) Is Not Null));

I've included a LastName field. Add whatever other fields or criteria are
wanted.
Change my table and field names to whatever yours are.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Jim said:
Fred,
Thanks so much for this. This works quite well. I've learned a whole
bunch in the past week thanks to you and Ken. May I trouble you with
one more question?

Can I take what I've learned and generate a 2nd table, one with the
dashed-SSNs. The only reason I ask, is the query I've created as per
your instructions, when I try to create a 2nd query using this one and
then a table from my ODBC DB, I get either both fields blank (Dash SSN
& PATID)on an equal join, or Dash SSN but blank PATID, with a
left-outer join. A right outer join gives me all the records in the
ODBC table and DashSSN blank.

Any thoughts, and again thanks for all your help.
Jim


"Fredg" <[email protected]> wrote in message
Jim,
This is a Select query, not an Update query, correct?

If you are using the QBE grid, you already have a SSN and a Pat_ID column.
In the next empty column, on the top Field line, write:
DashSSN:Left([SSN], 3) & "-" & Mid([SSN], 4, 2) & "-" & Right([SSN], 4)

I would also suggest you write:
Is Not Null
in the SSN criteria line to avoid an #Error if that SSN field is null.

When done, click on the View Tool button and select SQL view.
The query should read like this:

SELECT CDS.SSN, CDS.PAT_ID, Left([SSN], 3) & "-" & Mid([SSN], 4, 2) & "-" &
Right([SSN], 4) as DashSSN
FROM CDS
Where SSN is not null;

Click the Bang (!) tool button to run the query.

SSN Pat_ID DashSSN
123456789 101 123-45-6789


Note: You do not need to have the SSN field column in the query
unless you just want to compare the changes made.

SELECT CDS.PAT_ID, Left([SSN], 3) & "-" & Mid([SSN], 4, 2) & "-" &
Right([SSN], 4) as DashSSN
FROM CDS
Where SSN is not null;

Will work just as well.
Pat_ID DashSSN
101 123-45-6789

I hope you've got it now.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


"Fredg" <[email protected]> wrote in message
Click to expand...
Jim,
That's fine.
Just use the expression in a Select Query.

--
Fred

Fred and Ken,
I'm sorry to be such a bother, I'm not a total Access novice, but with
this one I'm over my head. Here's what I've done:

1. Create a query that selects all of my records from the table with
fields, SSN, and PAT_ID
2. My SQL statement looks like this
SELECT CDS.SSN, CDS.PAT_ID
FROM CDS;

I'm not sure where I should put the expression-- Left([SSN], 3) & "-"
& Mid([SSN], 4, 2) & "-" & Right([SSN], 4)--in the SQL statement.

Also, can I put this expression in the Criteria line of the SSN field
in the query? I tried Ken's update query suggestion, but the query
kept asking me for "Field to Update to."

I hate asking for such hand holding,
TIA,
Jim









DashSSN: Left([SSN], 3) & "-" & Mid([SSN], 4, 2) & "-" & Right([SSN],
4)
works better in here in the West. <g>


Jim,
To convert all the existing SSN's to include the hyphens, run an update
query.
After you update your table, then change your Input Mask so that
future entries will be saved with the mask.
Use:
000\-00\-0000;0;_
as the input mask. See Access Help.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Assuming that you're using a query as the basis of the report,
Click to expand...
add
a
calculated field to the query that converts the stored SSN to
Click to expand...
one
with
dashes:

DashSSN: Left([SSN], 2) & "-" & Mid([SSN], 3, 3) & "-" &
Right([SSN],
4)

Then use the DashSSN field in your report where you wish to display
the
SSN.

--
Ken Snell
<MS ACCESS MVP>

Greetings all,
I need to convert a field that has social security numbers stored as
XXXXXXXXX to XX-XXX-XXXX. When I use the Input mask, the "-" are
present when the table is displayed, however, when I use this
table
in
creating a report using Crystal Reports, my string is back to
XXXXXXXX.
I can manually put the "-" in the fields, and it works, but
Click to expand...
I
was
hoping for a less labor intensive way.
Thanks,
Jim
Click to expand...
Click to expand...
Click to expand...
 
Back
Top