Sorting bill of materials references
in [Help]
|
From: Lars P. Magnussen on 13 Mar 2010 12:47 In a bill of materials table I have references like C1, C2 ... C10, C11..C100 etc. Sorting the ref. field looks like this C1 C10 C100 C11 ... C19 C2 C20 C21 ... How to make it C1, C2 ... C9, C10 .... ? Best regards Lars P. Magnussen
From: Marco Pagliero on 13 Mar 2010 13:25 On 13 Mrz., 18:47, "Lars P. Magnussen" <l...(a)maglar.dk> wrote: > In a bill of materials table I have references like C1, C2 ... C10, > C11..C100 etc. > Sorting the ref. field looks like this > C1 > C10 > C100 > C11 > .. > C19 > C2 > C20 > C21 > .. > How to make it C1, C2 ... C9, C10 .... ? This order is the correct alphabetical order. Fault is the person who introduced the codes C1, C10, C100 instead of C001, C010, C100. You remove the C and take the numeric value of the rest: SortField=val(mid$(RefField,2)) This value will sort the way you expect. Greetings Marco
From: Lars P. Magnussen on 13 Mar 2010 15:03 >> How to make it C1, C2 ... C9, C10 .... ? > This order is the correct alphabetical order. Fault is the person who > introduced the codes C1, C10, C100 instead of C001, C010, C100. > Yes I agree, if we look at it from a database programmers view. But in electronic schematics, normally you don't assign component ref's C001 etc. So output from schematic is C1, C2..C10 etc. And bill of material list for production don't look like this (at least I have not seen that): 0805 capacitor C002-C008, C020, C100 or am I mistaken? > You remove the C and take the numeric value of the rest: > SortField=val(mid$(RefField,2)) > This value will sort the way you expect. > Yes, think I must do that to generate the lists and maybe afterwards remove the extra zeroes in production material. Best regards Lars P. Magnussen
From: Bob Quintal on 13 Mar 2010 15:58 "Lars P. Magnussen" <lpm(a)maglar.dk> wrote in news:4b9bef87$0$281$14726298(a)news.sunsite.dk: >>> How to make it C1, C2 ... C9, C10 .... ? >> This order is the correct alphabetical order. Fault is the person >> who introduced the codes C1, C10, C100 instead of C001, C010, >> C100. >> > Yes I agree, if we look at it from a database programmers view. > But in electronic schematics, normally you don't assign component > ref's C001 etc. So output from schematic is C1, C2..C10 etc. > And bill of material list for production don't look like this (at > least I have not seen that): > 0805 capacitor C002-C008, C020, C100 > or am I mistaken? > >> You remove the C and take the numeric value of the rest: >> SortField=val(mid$(RefField,2)) >> This value will sort the way you expect. >> > Yes, think I must do that to generate the lists and maybe > afterwards remove the extra zeroes in production material. > > Best regards > Lars P. Magnussen > What I did is to create a user-defined function that breaks the Reference Designator into its 2 or 3 parts. (e.g C23, C23A, C23B) and properly aligns the numeric portion. Unfortunately, the code is at work, and I'm at home enjoying the weekend. It went something like Public Function RefDesSort(RefDes as string as string) Dim RefType as String, RefNumber as string dim iPTR as integer for iPTR = 1 to len(RefDes) if isnumeric(mid(refdes,iPTR,1)) then RefNumber = Refnumber & mid(refdes,iPTR,1) else RefType = RefType & mid(refdes,iPTR,1) end if next iPTR 'now right justify the parts RefDesSort = right(space(3) & Reftype,3 ) _ & right(space(5) & refnumber.5) end function which would make "VR1" into " VR 1" Call the function in a query and sort using that column. > -- Bob Quintal PA is y I've altered my email address.
From: hbinc on 13 Mar 2010 18:23
On Mar 13, 6:47 pm, "Lars P. Magnussen" <l...(a)maglar.dk> wrote: > In a bill of materials table I have references like C1, C2 ... C10, > C11..C100 etc. > Sorting the ref. field looks like this > C1 > C10 > C100 > C11 > .. > C19 > C2 > C20 > C21 > .. > How to make it C1, C2 ... C9, C10 .... ? > > Best regards > Lars P. Magnussen If your SQL-string contains "ORDER BY Replace(" & fieldname & ",'C','',1) + 0" the field will be numerically sorted. Beware of the quotes. For readability I put spaces around the quotes: " ORDER BY Replace( " & fieldname & " , ' C ' , ' ' ,1) + 0 " d d d s s s s d HBInc. |