Update Query; But keep rest of field...

  • Thread starter Thread starter Tim Fierro
  • Start date Start date
T

Tim Fierro

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



--
 
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 said:
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.
Click to expand...


UPDATE tblItem SET tblItem.itemMfgItemNumber =
"Fit ABS" & Mid(tblItem.itemMfgItemNumber, 12)
WHERE tblItem.itemMfgItemNumber Like "Fitting ABS*"

Note the wild card *
 
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
Click to expand...

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.
 
Thanks to all who gave options to update my database.

This is what I ended up having to do with Microsoft Access 2003

UPDATE tblItem SET tblItem.itemDescription = "Fit ABS" &
Right([tblItem.itemDescription],Len([tblItem.itemDescription])-11)
WHERE Left(tblItem.itemDescription,11)="Fitting ABS";


One of the examples and suggestions given was to do this; but with my
version (2003), it seems I had to add the Brackets [] around a couple of
portions for it to work.

Tim


--



Tim Fierro - Parts Manager
Howard Moe Enterprises, Inc.
Ph: 360-538-1622 / Fax: 360-538-0225
e-Mail: (e-mail address removed)
 
Back
Top