Find and Replace

[Don Hamilton]

I have a Access database of parts that have part #'s that have been entered
with dash marks in all diffewrent places. Entries such as GMV-6-6B-8A and
some times the real number should be GMV6-6-B-8A and when you do search by
part number the dash marks have to be entered as in the data base or it will
not find. If I do a find and replace dashes with like a blank space will it
be searchable reguardless of where spaces are.

Thanks Don

 

[John W. Vinson]

I have a Access database of parts that have part #'s that have been entered
with dash marks in all diffewrent places. Entries such as GMV-6-6B-8A and
some times the real number should be GMV6-6-B-8A and when you do search by
part number the dash marks have to be entered as in the data base or it will
not find. If I do a find and replace dashes with like a blank space will it
be searchable reguardless of where spaces are.

Thanks Don

No, it will not.

The text strings "GMV 6 6B 8A" and "GMV66B8A" and "GMV6 6B 8A" are all
completely different text strings as far as Access is concerned; a blank -
just like a hyphen - is a meaningful character.

Is there *ANY* rationale to the format of this part number? Are there any
limits to the size or content of the substrings? If you were to strip out all
the punctuation - not replacing it with blanks - are you going to have trouble
with ambiguity, e.g. might "GMV-6-6B-8A" and "GMV- -66B-8A" both be valid but
different part numbers?

 

[Don Hamilton]

No, it will not.

The text strings "GMV 6 6B 8A" and "GMV66B8A" and "GMV6 6B 8A" are all
completely different text strings as far as Access is concerned; a blank -
just like a hyphen - is a meaningful character.

Is there *ANY* rationale to the format of this part number? Are there any
limits to the size or content of the substrings? If you were to strip out
all
the punctuation - not replacing it with blanks - are you going to have
trouble
with ambiguity, e.g. might "GMV-6-6B-8A" and "GMV- -66B-8A" both be valid
but
different part numbers?

If there is a way to just remove all punctuation would be great is that
possible? Some strings have more than others. When the data was entered it
was not known that there was differences in hyphen placement from one
company to the other. The part numbers in the GMV66B8A would be great if
there is a way. All combinations of assorted hyphens are all the same part.
This data base only includew about 7000 parts but would take time to edit
and remove all hyphens.

Thanks Don ( very new to Access)

 

[John W. Vinson]

If there is a way to just remove all punctuation would be great is that
possible? Some strings have more than others. When the data was entered it
was not known that there was differences in hyphen placement from one
company to the other. The part numbers in the GMV66B8A would be great if
there is a way. All combinations of assorted hyphens are all the same part.
This data base only includew about 7000 parts but would take time to edit
and remove all hyphens.

Thanks Don ( very new to Access)

You can get rid of all the hyphens (and, with some more effort, all
punctuation) using the Replace() function in an Update query. BACK UP YOUR
DATABASE!!! - this is a one-way trap door (into a maze of twisty little
tunnels, all alike...)

Create a Query based on your table. Change it to an update query (using the
query type tool or the Query menu option). Select only the PartNumber field;
on the Update To line under it put

=Replace([PartNumber], "-", "")

Use your actual fieldname, and don't omit the square brackets (otherwise it
will update all the part numbers to the text string "PartNumber"!)

Run the query by clicking the ! icon. Check your data. If the partnumber is a
link to other tables you'll need to either define the relationship between the
tables to Cascade updates, or else rerun the query in all the affected tables.