Re: Update Query; But keep rest of field...
- From: fredg <fgutkind@xxxxxxxxxxxxxxx>
- Date: Wed, 6 Dec 2006 14:31:40 -0800
On Wed, 6 Dec 2006 13:06:04 -0800, Tim Fierro wrote:
Hello,
Using the Design View to create a query, here is what I have;
UPDATE tblItem SET tblItem.itemMfgItemNumber = "Fit ABS"
WHERE (((tblItem.itemMfgItemNumber)="Fitting ABS"));
That of course didn't work like I thought. :-) I need a bit of help
understanding how to udpate a field in such a way that only the first part
is changed, and the rest of the string in the field is left intact after the
query updated.
The goal is to find all items that START with "Fitting ABS" and change them
all to "Fit ABS".
Example:
Fitting ABS 1-1/2" Fitting Clean-Out Adapter
Fitting ABS 1-1/2" In-Line Vent (One-Way Vent)
The above would be found and it would change them to;
Fit ABS 1-1/2" Fitting Clean-Out Adapter
Fit ABS 1-1/2" In-Line Vent (One-Way Vent)
Basically want to change a portion of the field, but not the whole field.
If I can figure the query for that, I can then adapt to other areas I want
to change such as;
Fitting PVC Sch40 1" 90 ELL SxS
Fitting PVC Sch80 1-1/2" 45 ELL S x S
and have a query that will change those above to;
Fit P40 1" 90 ELL SxS
Fit P80 1-1/2" 45 ELL S x S
I am trying to shorten descriptions in the database for when I am logging
parts used and purchasing parts; I can determine easier what the item is.
Right now I have to move the cursor over to the right in some of the screen
layouts, just to determine what type of fitting it is. Most of the
information description can be cut to indicate quicker what type of fitting
it is by using the above.
Any tip on how to have the query just match the BEGINNING of a matched
field, yet when it updates, it only changes the beginning and keeps the
ending?
Tim
So you just wish to change the word Fitting in each record to Fit?
Here are 2 ways to do this. You can:
1) UPDATE tblItem SET tblItem.itemMfgItemNumber =
Replace([itemMfgItemNumber],"Fitting","Fit");
No need for criteria unless the word "Fitting" can be elsewhere in the
field.
2) Or...
UPDATE tblItem SET tblItem.itemMfgItemNumber ="Fit" &
Mid([itemMfgItemNumber],4) Where [itemMfgItemNumber] Like "Fitting" &
"*";
Only records that begin with Fitting will be changed.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.
- References:
- Update Query; But keep rest of field...
- From: Tim Fierro
- Update Query; But keep rest of field...
- Prev by Date: Re: datediff("m",date1,date2)does not work on any date other than
- Next by Date: Re: Query asks for parameter with no connection to the query.
- Previous by thread: Re: Update Query; But keep rest of field...
- Next by thread: Re: Update Query; But keep rest of field...
- Index(es):
Relevant Pages
|
