Find and replace - Newbie question

[Nate]

Hello folks,

I am in the process of moving records from an older Access DB to a newer one
which has a slightly different design. In order for the move to succeed, I
need to be able to remove a number of different text strings from a field.
Specifically, I need to remove file suffixes and extensions but leave the
rest of the data intact. For example: the filename field may contain a record
like "holidayseg01_320.flv". I need to remove the "_320.flv" There are
another 15-20 suffixes w/ extensions that I need to remove and I'd like to
find a proceedural way to set this up so that I can reproduce my results
without having to spend half the day using Find and Replace for each
extension. I've looked through some of the discussions but was unable to find
(or maybe understand) the answer I need. Can somebody walk me through? You
may need to dumb it down a bit as I'm not fluent in SQL or VB, but I do know
my way around the Access interface...

Thanks in Advance!

 

[John W. Vinson]

Hello folks,

I am in the process of moving records from an older Access DB to a newer one
which has a slightly different design. In order for the move to succeed, I
need to be able to remove a number of different text strings from a field.
Specifically, I need to remove file suffixes and extensions but leave the
rest of the data intact. For example: the filename field may contain a record
like "holidayseg01_320.flv". I need to remove the "_320.flv" There are
another 15-20 suffixes w/ extensions that I need to remove and I'd like to
find a proceedural way to set this up so that I can reproduce my results
without having to spend half the day using Find and Replace for each
extension. I've looked through some of the discussions but was unable to find
(or maybe understand) the answer I need. Can somebody walk me through? You
may need to dumb it down a bit as I'm not fluent in SQL or VB, but I do know
my way around the Access interface...

Thanks in Advance!

Well... you'll need to amplify your question a bit. If there is something in
common with all these suffixes you can do it... for instance, if you want to
remove ALL text starting with an underscore, you could update the field to

Left([Filename] & "_", InStr([Filename] & "_", "_") - 1)

However that may be too drastic (e.g. if you have a filename
"fourth_of_july_320.flv" it would update it to "fourth").

You can run 15-20 Update queries updating the field to

Replace([filename], "_320.flv", "")

with a criterion on the filename field of

LIKE "*_320.flv"

editing the Replace function and criterion for each suffix; that's at least
marginally better than editing each record.

 

[Nate]

Thanks! The update querry works great. I had been trying to use the Replace
function in a module but I couldn't get the syntax right - I definately need
to do some VB tutorials. The important thing for me is that I can save all of
these querries and tie them together in a macro. That makes this process much
easier as there are a number of steps involved in moving this data. Thanks
again for your help.

Hello folks,

I am in the process of moving records from an older Access DB to a newer one
which has a slightly different design. In order for the move to succeed, I
need to be able to remove a number of different text strings from a field.
Specifically, I need to remove file suffixes and extensions but leave the
rest of the data intact. For example: the filename field may contain a record
like "holidayseg01_320.flv". I need to remove the "_320.flv" There are
another 15-20 suffixes w/ extensions that I need to remove and I'd like to
find a proceedural way to set this up so that I can reproduce my results
without having to spend half the day using Find and Replace for each
extension. I've looked through some of the discussions but was unable to find
(or maybe understand) the answer I need. Can somebody walk me through? You
may need to dumb it down a bit as I'm not fluent in SQL or VB, but I do know
my way around the Access interface...

Thanks in Advance!

Well... you'll need to amplify your question a bit. If there is something in
common with all these suffixes you can do it... for instance, if you want to
remove ALL text starting with an underscore, you could update the field to

Left([Filename] & "_", InStr([Filename] & "_", "_") - 1)

However that may be too drastic (e.g. if you have a filename
"fourth_of_july_320.flv" it would update it to "fourth").

You can run 15-20 Update queries updating the field to

Replace([filename], "_320.flv", "")

with a criterion on the filename field of

LIKE "*_320.flv"

editing the Replace function and criterion for each suffix; that's at least
marginally better than editing each record.