Data from one field to another

L

Lesah

I have a very old Access database, that started it's life as an old Q&A
database. Social Security field is the one in which I do not want
duplicates. I have a search filter, (will post a filter question on another
thread), I use to find if someone is already entered. The Q&A format was
###-##-####. But after conversion you could only find them using the dashes.
Then the format from Access stored them in another way, and they cannot be
found with dashes. There are about 10,000 records with different formats,
making search more difficult. Can I format a SS field the way I want it, and
then transfer the data from the old field into that so the formatting will
all be the same?
 
R

Rick Brandt

Lesah said:
I have a very old Access database, that started it's life as an old
Q&A database. Social Security field is the one in which I do not want
duplicates. I have a search filter, (will post a filter question on
another thread), I use to find if someone is already entered. The
Q&A format was ###-##-####. But after conversion you could only find
them using the dashes. Then the format from Access stored them in
another way, and they cannot be found with dashes. There are about
10,000 records with different formats, making search more difficult.
Can I format a SS field the way I want it, and then transfer the data
from the old field into that so the formatting will all be the same?
Click to expand...

If you run an update query using the Replace() function you should be able to
replace all instances of "-" with "". That would result in all of the rows
having no dashes. If you would prefer that they all have the dash then you
should be able to run another update query using the format property to put them
back in.
 
L

Lesah

Thanks Rick. I understand what you are saying and it makes sense. But like
most replies on this forum, it is a little over my head. I don't know how to
write modules. Could you possibly post the syntax, (if that is the right
word), to accomplish this? Unfortunately I have only learned how to use the
basic tools and formats provided by the program.
 
L

Lesah

I tried that and it almost worked, then I got "key violations" error. This
field is not a primary field, allows duplicates, and there are no
relationships associated with this table. I have searched the knowledge base
about key violations and did not find an article directly addressing this.
This will be my last question I promise.
 
Top