Remove dash from soc sce #

[Paul]

I would to remove dashes from 111-22-5555.
I am importing text file and some of the numbers have dashes and some
don't

Thanks

[Allen Browne]

Use Replace() in an Update query to lose the dashes.

1. Create a query into this table.

2. Add your field to the output grid.
In the Criteria row under this field, enter:
Is Not Null

3. Change it to an Update query (Update on Query menu.)
Access adds an Update row to the grid.

4. In the Update row, enter:
Replace([SSN], "-", "")
substituting your field name for SSN.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Paul" <pa**@tdsd.co m> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

I would to remove dashes from 111-22-5555.
I am importing text file and some of the numbers have dashes and some
don't

[Paul]

Replace Function doesn't work in a query in Access 2000!

[Wayne Morgan]

I believe it may work in later versions of Access 2000 (i.e. all service
packs applied), but if not, then create a user defined function and name it
something like MyReplace. Call this function from the query, passing the
parameters. In the user defined function, use the Replace function to do the
work and assign the corrected string to the user defined function.

--
Wayne Morgan
MS Access MVP
"Paul" <pa**@tdsd.co m> wrote in message
news:11******** *************@g 44g2000cwa.goog legroups.com...

Replace Function doesn't work in a query in Access 2000!

[Allen Browne]

There is a Microsoft article on that:
http://support.microsoft.com/?id=225956
It suggests writing a little wrapper function that you can use in a query.

If you can't get that going, and want to code your own:

Function ReplaceX(strExp r As String, strFind As String, strReplace As
String, Optional lngStart As Long = 1) As String
Dim strOut As String
Dim lngLenExpr As Long
Dim lngLenFind As Long
Dim lng As Long

lngLenExpr = Len(strExpr)
lngLenFind = Len(strFind)

If (lngLenExpr > 0) And (lngLenFind > 0) And (lngLenExpr >= lngStart)
Then
lng = lngStart
If lng > 1 Then
strOut = Left$(strExpr, lng - 1)
End If
Do While lng <= lngLenExpr
If Mid(strExpr, lng, lngLenFind) = strFind Then
strOut = strOut & strReplace
lng = lng + lngLenFind
Else
strOut = strOut & Mid(strExpr, lng, 1)
lng = lng + 1
End If
Loop
ReplaceX = strOut
End If
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Paul" <pa**@tdsd.co m> wrote in message
news:11******** *************@g 44g2000cwa.goog legroups.com...

Replace Function doesn't work in a query in Access 2000!