how do I find the dash?

[rschultz]

A fellow employee created a table that has ss#s. Different people have been entering the data. Some added the dashs others did not. How do I run a query that checks to see if the 4th character and the 7th character are dashs and if they are not insert them so all the ss#s will have the same correct format?

 

[dcx693]

IMHO, it's best not to store the dashes with the SSNs since they can always be formatted to display the dashes, and you'll save two bytes of data per SSN in your database.

Here's how you can remove the dashes. You can use a query with the Mid() function to look for the "-" character within the SSN like this Mid([SSN],4,1)="-" . You can run it as an update query and replace the character with nothing and that will strip it out. Then use the Mid() function in a query again to search for a "-" in the 6th position (not the seventh since you've already removed the dash from the 4th position so they've all shifted one over).

If you're intent on inserting dashes, here's a way. You could use the Mid() function again to test for the "-", then use the IIf function like this to insert the "-" like this:
IIf(Mid([SSN],4,1)="-",[SSN],Left([SSN],3)&"-"&Right([SSN],Len([SSN])-3)

Then do something similar to insert the second dash.

 

[raskew]

Function Removeomatic(), shown below, will remove all instances of a specific character or string from a target string.

Copy/paste the code to a new module, play with the function until you're comfortable with it, then BACKUP YOUR TABLE. Now, create an update query. In the Update To: put Removeomatic([SSN], "-").

Run the query and voila--no dashes.

Function Removeomatic(ByVal pStr As String, ByVal pchar As String) As String
'*******************************************
'Name:      Removeomatic (Function)
'Purpose:   Removed specified characters from a string.
'Inputs:    from debug window:  ? Removeomatic("123-45-6789", "-")
'Output:    123456789
'*******************************************

Dim strHold As String
strHold = RTrim(pStr)
Do While InStr(strHold, pchar) > 0
  strHold = Left(strHold, InStr(strHold, pchar) - 1) & Mid(strHold, InStr(strHold, pchar) + 1)
Loop
Removeomatic = strHold
End Function

 

[rschultz]

Thanks - I'll try the suggestions - it's nice to have a couple alternatives