Need to remove hyphens from SSN

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there! Using A02 on XP. Have client files that I'm bringing back in to
Access and running a compare to what went out. When some clients add records
they key in hyphens (even though asked not to in the instructions, you know
how that goes). I need no hyphens. I could parse them up and reconstitute but
also first need to be able to recognize if there ARE any hyphens and then, if
so, remove them. Maybe an update query? Would really, really appreciate any
help or advice on this. Is a query the best place for me to do it? Thanks in
advance for your time!
 
I see two options:
1) Add an inputmask to the textbox on the form. This will prevent them from
entering anything but nine numeric digits.
2) Use the Replace function: strNewVal = Replace(strOldVal, "-","")

HTH,
Barry
 
Thanks John, I used the Replace([SSN],"-","") in an update query and bundled
it in with my other 'cleanup' queries. Works great! Thanks again for
helping.
--
Bonnie


John Spencer said:
UPDATE YourTable
SET [SSN] = Replace([SSN],"-","")
WHERE [SSN] Like "*-*"


Bonnie said:
Hi there! Using A02 on XP. Have client files that I'm bringing back in to
Access and running a compare to what went out. When some clients add
records
they key in hyphens (even though asked not to in the instructions, you
know
how that goes). I need no hyphens. I could parse them up and reconstitute
but
also first need to be able to recognize if there ARE any hyphens and then,
if
so, remove them. Maybe an update query? Would really, really appreciate
any
help or advice on this. Is a query the best place for me to do it? Thanks
in
advance for your time!
Click to expand...
Click to expand...
 
I get an error when trying to duplicate this expression. Access don't like
"Replace" for some reason... "Undefined function 'Replace' in expression"

John Spencer said:
UPDATE YourTable
SET [SSN] = Replace([SSN],"-","")
WHERE [SSN] Like "*-*"


Bonnie said:
Hi there! Using A02 on XP. Have client files that I'm bringing back in to
Access and running a compare to what went out. When some clients add
records
they key in hyphens (even though asked not to in the instructions, you
know
how that goes). I need no hyphens. I could parse them up and reconstitute
but
also first need to be able to recognize if there ARE any hyphens and then,
if
so, remove them. Maybe an update query? Would really, really appreciate
any
help or advice on this. Is a query the best place for me to do it? Thanks
in
advance for your time!
Click to expand...
Click to expand...
 
1. What version of Access are you using? Replace did not exist before Access
2000.
2. Have you check the References (Tools>References in the code window) for
any MISSING references?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



dtoney said:
I get an error when trying to duplicate this expression. Access don't like
"Replace" for some reason... "Undefined function 'Replace' in expression"

John Spencer said:
UPDATE YourTable
SET [SSN] = Replace([SSN],"-","")
WHERE [SSN] Like "*-*"


Bonnie said:
Hi there! Using A02 on XP. Have client files that I'm bringing back in to
Access and running a compare to what went out. When some clients add
records
they key in hyphens (even though asked not to in the instructions, you
know
how that goes). I need no hyphens. I could parse them up and reconstitute
but
also first need to be able to recognize if there ARE any hyphens and then,
if
so, remove them. Maybe an update query? Would really, really appreciate
any
help or advice on this. Is a query the best place for me to do it? Thanks
in
advance for your time!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top