2 Part Question

  • Thread starter Thread starter TeeSee
  • Start date Start date
T

TeeSee

I have a table containing apart from other fields one with a part
number the last character denotes the supplier.
The next significant field contains (in different positions) the
manufacturers name. How can I ....

1) strip away the last charcter in the first field and replace it with
another character

2) Find and delete a string that would be the manufacturers name, and
replace it with a new string that would be a new manufacturers name.

The rest of the description is the same for both manufacturers.

Would you "do" this by query if feasible otherwise be as explicit as
possible. Hints don't always work for me.

Many thanks
 
I have a table containing apart from other fields one with a part
number the last character denotes the supplier.
Click to expand...

:-{( Then you've violated the principle that fields should be atomic.
I'd really suggest having a separate supplier field.
The next significant field contains (in different positions) the
manufacturers name. How can I ....
Click to expand...

"Next significant field"...???
1) strip away the last charcter in the first field and replace it with
another character
Click to expand...

Update the field to

Left([fieldname], Len([fieldname]) - 1 & "X"

to replace the last character with X.
2) Find and delete a string that would be the manufacturers name, and
replace it with a new string that would be a new manufacturers name.
Click to expand...

Again... storing the manufacturer name in a separate field (preferably a
numeric ManufacturerID linked to a table of Manufacturers) will be a heck of a
lot easier to maintain.
The rest of the description is the same for both manufacturers.
Click to expand...

Update the field to

Replace([fieldname], "old manufacturer name", "new manufacturer name")

If the old manufacture is (say) BAS, this will replace the text string "bas"
wherever it appears - even if it's not the manufacturer name, e.g. "sturdy
weighted base" will be replaced by "sturdy weighted New Manufacturere".
Would you "do" this by query if feasible otherwise be as explicit as
possible. Hints don't always work for me.
Click to expand...

They don't work very well for us either. You chose not to post any tablenames
or fieldnames, so nobody here will be able to write a specific query for you.

John W. Vinson [MVP]
 
I have a table containing apart from other fields one with a part
number the last character denotes the supplier.
Click to expand...

:-{( Then you've violated the principle that fields should be atomic.
I'd really suggest having a separate supplier field.
The next significant field contains (in different positions) the
manufacturers name. How can I ....
Click to expand...

"Next significant field"...???
1) strip away the last charcter in the first field and replace it with
another character
Click to expand...

Update the field to

Left([fieldname], Len([fieldname]) - 1 & "X"

to replace the last character with X.
2) Find and delete a string that would be the manufacturers name, and
replace it with a new string that would be a new manufacturers name.
Click to expand...

Again... storing the manufacturer name in a separate field (preferably a
numeric ManufacturerID linked to a table of Manufacturers) will be a heck of a
lot easier to maintain.
The rest of the description is the same for both manufacturers.
Click to expand...

Update the field to

Replace([fieldname], "old manufacturer name", "new manufacturer name")

If the old manufacture is (say) BAS, this will replace the text string "bas"
wherever it appears - even if it's not the manufacturer name, e.g. "sturdy
weighted base" will be replaced by "sturdy weighted New Manufacturere".
Would you "do" this by query if feasible otherwise be as explicit as
possible. Hints don't always work for me.
Click to expand...

They don't work very well for us either. You chose not to post any tablenames
or fieldnames, so nobody here will be able to write a specific query for you.

John W. Vinson [MVP]
Click to expand...

Thanks for the reply John. While I fully understand your suggestion
about a separate table for the manufacturer I obviously did not exlain
adequately.
The manufacturers name is actually used as a part of the item
description in a table called Master. e.g.
tblMaster, txtItemNumber, txtItemDescription

FS10030A "ABC company unique item"
FS10005A "Unique ABC company item"
I have wrinten the above info to a new table which I now want to
doctor as 1 and 2 above. You have given solution to create the new
txtItemNumber so now I want to replace the ABC no matter what position
in the field with XYZ. All other data remains constant

FS10003X "XYZ company unique item"
FS10005X "Unique XYZ company item"

I'm exhausted after explaining this ... Hope it now makes more sense.
Touche regarding the "hints"

Thanks again.
 
The manufacturers name is actually used as a part of the item
description in a table called Master. e.g.
tblMaster, txtItemNumber, txtItemDescription

FS10030A "ABC company unique item"
FS10005A "Unique ABC company item"
I have wrinten the above info to a new table which I now want to
doctor as 1 and 2 above. You have given solution to create the new
txtItemNumber so now I want to replace the ABC no matter what position
in the field with XYZ. All other data remains constant

FS10003X "XYZ company unique item"
FS10005X "Unique XYZ company item"
Click to expand...

UPDATE tblMaster
SET txtItemDescription = Replace([txtItemDescription], "ABC", "XYZ")
WHERE txtItemDescription LIKE "*ABC*";

should do it, with the caveat noted above that if the company name appears
accidentally in some other context it will be replaced there too. "Unique ABC
Company crabcakes" will become "Unique XYZ Company crXYZakes" - but that's the
price you pay for nonatomicity.

John W. Vinson [MVP]
 
Back
Top