Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

I need help formatting a SSN field in VB6

Hi Experts,
I need help formatting an SSN field in my VB6 application.  
Currently, the SSN's stored in my database have dashes in them.  I want to replace the dashes.  
I also want to make sure the SSN's are always 9 characters long.

This is a sample of what some SSN's look like in my database:
589-05-9945
28-25-1525

What I want the SSN's to look like after the formatting:
589059945
028251525

How can I accomplish this?

Thanks in advance,
mrotor
0
mainrotor
Asked:
mainrotor
2 Solutions
 
aikimarkCommented:
Probably the easiest way is to execute an update query like this:
Update MyTable
Set SSN = Right("000000000" & Replace(SSN, "-", ""), 9)

Open in new window

0
 
mainrotorAuthor Commented:
I need to do it in VB6.  I don't want to update the whole table. How can I do it in VB6?


mrotor
0
 
Dan CraciunIT ConsultantCommented:
I'll leave the "get data from db" and "write data to db" to you :)
Dim oldSSN As String = "28-25-1525"
Dim newSSN As String

newSSN = Replace(oldSSN, "-", "")
newSSN = newSSN.PadLeft(9, "0")

Open in new window

HTH,
Dan
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.

 
aikimarkCommented:
Just remove the SQL part of the query.
SSN = Right("000000000" & Replace(SSN, "-", ""), 9)

Open in new window

0
 
aikimarkCommented:
@Dan

PadLeft is a .Net method and not applicable to mainrotor's VB6 run-time environment.
0
 
Dan CraciunIT ConsultantCommented:
Yup. I always get them mixed up.
If you don't use them often you quickly forget the difference between VB, VBS, VBA, VB.NET...
0
 
HooKooDooKuCommented:
Modification to Dan's suggestion that will work in VB6
Dim oldSSN As String = "28-25-1525"
Dim newSSN As String

newSSN = Replace(oldSSN, "-", "")
If Len( newSSN ) < 9 Then
  newSSN = newSSN & String$( 9 - Len(newSSN), "0" )
End If

Open in new window

0
 
Martin LissOlder than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for aikimark's comment #a40005868

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now