need help removing zeros with Left or right??

B

Barry A&P

way back when everything was typewritten Bell Used partnumbers like
204-040-032-1 that was all find and dandy well now they changed all the
endings (rightfully so) to -001 so i need to trim them out..

My PartNumber Table has two columns PartNumber and PartNumberTrimmed
i am trying to create a update Query to populate the PartNumberTrimmed field.

the issue is a lot of partnumbers that i dont want to change follow a
different format. here is some sample data

AN310-5 Dont Change
MS233465-28 Dont Change
204-040-003-21
204-005-003-001 Remove 2 Zeros
204-005-003-056R Remove 1 Zero and put R in revision Field
204-005-003-017 Remove 1 Zero

So all partnumbers that begin with the format ###-###-###- i need to take
the last set of digits and trim leading zeros and move any trailing Alpha's
to another field.. PartnumberRevision or something.

so that
204-005-003-001 becomes 204-005-003-1 and
204-005-003-024R becomes 204-005-003-24 R
im sure this is much easier than it is in my head
i have not dealt with a query like this yed so i am excited to see some
suggestions
thanks for any help
Barry
 
M

Marshall Barton

Barry said:
way back when everything was typewritten Bell Used partnumbers like
204-040-032-1 that was all find and dandy well now they changed all the
endings (rightfully so) to -001 so i need to trim them out..

My PartNumber Table has two columns PartNumber and PartNumberTrimmed
i am trying to create a update Query to populate the PartNumberTrimmed field.

the issue is a lot of partnumbers that i dont want to change follow a
different format. here is some sample data

AN310-5 Dont Change
MS233465-28 Dont Change
204-040-003-21
204-005-003-001 Remove 2 Zeros
204-005-003-056R Remove 1 Zero and put R in revision Field
204-005-003-017 Remove 1 Zero

So all partnumbers that begin with the format ###-###-###- i need to take
the last set of digits and trim leading zeros and move any trailing Alpha's
to another field.. PartnumberRevision or something.

so that
204-005-003-001 becomes 204-005-003-1 and
204-005-003-024R becomes 204-005-003-24 R
im sure this is much easier than it is in my head
i have not dealt with a query like this yed so i am excited to see some
suggestions
Click to expand...


I think this should do tyhat:

UPDATE PartNumbers
SET PartNumberTrimmed = Left(PartNumber,12) &
Val(Mid(PartNumber,13,3)), PartnumberRevision =
Mid(Partnumber,16)
WHERE Partnumber Like "###-###-###-###*"
 
K

KARL DEWEY

It seems that all you want to change have 3 dashes so use this as criteria --
WHERE Len([PartNumber]) - Len(Replace(Replace(Replace([PartNumber], "-",
""), "-", ""), "-", "")) = 3
This measure PartNumber with and without dashes.

Trimmed PartNumber --
Left([PartNumber], InstrRev([PartNumber], "-")-1) & Val(Mid([PartNumber],
InstrRev([PartNumber], "-")+1)

Revision --
IIF(Right([PartNumber], 1) = "R", "R", Null)

Then run update query on PartNumberTrimmed = PartNumber with criteria = Null.
 
B

Barry A&P

Marshall

youre amazing
35K records .32 seconds

Got it with
UPDATE 2009BellPricesBackup
SET PartNumberTrimmed = Left(PARTNUMBERBELL,12) &
Val(Mid(PARTNUMBERBELL,13,3)), PartnumberRevision =
Mid(PARTNUMBERBELL,16)
WHERE PARTNUMBERBELL Like "###-###-###-###*";

Val() Ill ad it to my list of goodies
Sorry i took so long i dont get notifications anymore and only recently
figured out how to find old posts

Have a great new year
Barry
 
Top