Re: Update Query; But keep rest of field...

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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: tim@xxxxxxxxxxxxxxxxxxxxxxxxx


"Tim Fierro" <noemail@xxxxxxxxxxx> wrote in message
news:JcudnSs1aN4krerYnZ2dnUVZ_tOmnZ2d@xxxxxxxxxxxxxx
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



--





.

Relevant Pages

  • Re: Update Query; But keep rest of field...
    ... Using the Design View to create a query, ... UPDATE tblItem SET tblItem.itemMfgItemNumber = "Fit ABS" ... The goal is to find all items that START with "Fitting ABS" and change them ... Fitting PVC Sch40 1" 90 ELL SxS ...
    (microsoft.public.access.queries)
  • Re: Update Query; But keep rest of field...
    ... 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$will tell you if the beginning matches the eleven characters in "Fitting ABS". ... Will take "Fit ABS", and add everything except the first eleven characters, of tblItem.to it. ... Using the Design View to create a query, ...
    (microsoft.public.access.queries)
  • Update Query; But keep rest of field...
    ... Using the Design View to create a query, ... UPDATE tblItem SET tblItem.itemMfgItemNumber = "Fit ABS" ... The goal is to find all items that START with "Fitting ABS" and change them ... Fitting PVC Sch40 1" 90 ELL SxS ...
    (microsoft.public.access.queries)
  • Re: Update Query; But keep rest of field...
    ... UPDATE tblItem SET tblItem.itemMfgItemNumber = "Fit ABS" ... The goal is to find all items that START with "Fitting ABS" and change them ... Fitting PVC Sch40 1" 90 ELL SxS ...
    (microsoft.public.access.queries)
  • Re: Singular matrix in Invert_RtR
    ... Singular matrix in Invert_RtR ... The problem is that Gnuplot's fit uses a procedure known as Marquard-Levenberg algorithm, ... There is an alternative fitting algorithm, Simplex by Nelson & Mead, which is much more stable and can also minimise Chi^2 or the median of residuals in those cases where minimising the sum of squares is statistically inappropriate. ... Unfortunately, Simplex can not directly calculate the standard deviations for the parameters, that is probably the reason why many scientific fitting programs do not use it. ...
    (comp.graphics.apps.gnuplot)