Solved

I need help formatting a SSN field in VB6

Posted on 2014-04-16
10
235 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
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
 
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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

828 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