Re: Update Query; But keep rest of field...
- From: Phil Smith <phil@xxxxxxxxxxx>
- Date: Wed, 06 Dec 2006 13:49:40 -0800
Look up Left$(), Right$() and LEN() in help.
Left$ and Right$ act like a scissors, counting from left or right, and cutting the string at that point, returning just that portion of the string.
Comparing "Fitting ABS" with Left$(tblItem.[itemMfgItemNumber],11) will tell you if the beginning matches the eleven characters in "Fitting ABS". If it does, then you create a new string using concatenation.
"Fit ABS"&(right$(tblItem.[itemMfgItemNumber],len(tblItem.[itemMfgItemNumber])-11)
Will take "Fit ABS", and add everything except the first eleven characters, of tblItem.[itemMfgItemNumber] to it.
Your query would look something like:
UPDATE tblItem SET tblItem.itemMfgItemNumber = "Fit ABS"&right$(tblItem.itemMfgItemNumber,len(tblItem.itemMfgItemNumber)-11)
WHERE Left$(tblItem.itemMfgItemNumber,11)="Fitting ABS";
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
- References:
- Update Query; But keep rest of field...
- From: Tim Fierro
- Update Query; But keep rest of field...
- Prev by Date: Re: Opening a query in SQL View
- Next by Date: Re: Calculate Amount in stock
- Previous by thread: Update Query; But keep rest of field...
- Next by thread: Re: Update Query; But keep rest of field...
- Index(es):
Relevant Pages
|
