Data from one field to another

[Guest]

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?

 

[Rick Brandt]

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?

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.

 

[Guest]

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.

 

[Rick Brandt]

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.

This isn't code and has nothing to do with modules. What I suggested was
running an update query against your table. In that update query you can set
the field equal to the result of passing itself through the Replace function. I
cannot post what you would see in the query designer in these forums, but the
SQL of such a query would look something like...

UPDATE TableName
SET FieldName = Replace(FieldName, "-", "")

Essentially this says update FieldName equal to itself with the dashes stripped
out.

Test on a copy of your table as update queries done incorrectly could lose all
your data.

 

[Guest]

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.

 

[Rick Brandt]

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.

Hmmm. Perhaps try making the query a MakeTable query so that the results end up
in a totally new table (which should have no rules to break). Then you can
replace your table with the new one.