Solved

I need help formatting a SSN field in VB6

Posted on 2014-04-16
10
236 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 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Auto-indent certain lines in Notepad++ 10 145
Vector, list Questions R 6 49
Java Inheritance super keyword use 8 40
Win 10 Automation Error with .doCmd in Access 2 31
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.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

730 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