?
Solved

I need help formatting a SSN field in VB6

Posted on 2014-04-16
10
Medium Priority
?
248 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
8 Comments
 
LVL 46

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 35

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 46

Assisted Solution

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

Open in new window

0
 
LVL 46

Expert Comment

by:aikimark
ID: 40005869
@Dan

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

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

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Why WooCommerce is one of the majorly favored choices when it comes to having an eCommerce store. This article will acquaint you with some reasons that I believe make it one of the best eCommerce platforms available.
Simple Linear Regression
Introduction to Processes

621 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