If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
need help removing zeros with Left or right??
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 |
#2
|
|||
|
|||
need help removing zeros with Left or right??
Barry A&P wrote:
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 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 "###-###-###-###*" -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
need help removing zeros with Left or right??
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. -- Build a little, test a little. "Barry A&P" wrote: 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 |
#4
|
|||
|
|||
need help removing zeros with Left or right??
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 "Marshall Barton" wrote: Barry A&P wrote: 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 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 "###-###-###-###*" -- Marsh MVP [MS Access] . |
Thread Tools | |
Display Modes | |
|
|