How do I remove hyphens or periods in name using access 2000?

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

Guest

When down loading employee's names to a table you have periods and hyphens in
names and I need to remove them.
Ex: John Brown-Smith to read John Brown Smith
Ex: Anthony Contreras, Jr. to read Anthony Contreras Jr
Have you come across a way to easily remove these characters from a text
field.
 
Create a function that uses the Replace() function for every unwanted
character.
string1 = Replace(string,"-"," ")
string2 = Replace(string1,","," ")
...
 
Or do it in a single step:

string = Replace(Replace(string,"-"," "), ",", " ")
 
Use a calculated field name in your query. The example below will replace
all hypens and periods with spaces. It may be that you want to replace
periods with a zero lenth string. If so, use the second version

EmpName: Replace(Replace([EmployeeName],"."," "),"-"," ")
EmpName: Replace(Replace([EmployeeName],".",""),"-"," ")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

separating first and last names when they are in the same cell 4
how do I create supervisor hierarchy with the access table 2
Separate Names 3
How to remove characters in a cell that precede a specific hyphen 17
How can I allow hyphen (or dash) in this validation rule? 1
How do I search for a comma from the right so I can separate last name first name? 3
Can I use 'Like' or '*' operater is a parameter query? 2
Pick out the last word of text in Access query 2

Back
Top