Solved

I need help formatting a SSN field in VB6

Posted on 2014-04-16
10
233 Views
Last Modified: 2014-05-11
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
Comment
Question by:mainrotor
10 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 40005848
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
 

Author Comment

by:mainrotor
ID: 40005852
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
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40005853
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
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 250 total points
ID: 40005868
Just remove the SQL part of the query.
SSN = Right("000000000" & Replace(SSN, "-", ""), 9)

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 45

Expert Comment

by:aikimark
ID: 40005869
@Dan

PadLeft is a .Net method and not applicable to mainrotor's VB6 run-time environment.
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40005889
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
 
LVL 16

Accepted Solution

by:
HooKooDooKu earned 250 total points
ID: 40006588
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40057276
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
allswap challenge 6 79
passing a value with stream reader AFTER a ";" 3 58
parse convert xml feed to text (python) 2 69
C# application error "Parameter Is Not Valid". 3 79
This is an explanation of a simple data model to help parse a JSON feed
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now