 | |
Sorting bill of materials references
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Sorting bill of materials references -
03-13-2010
, 11:47 AM
#2
| |||
| |||
|
|
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 |
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
#3
| |||
| |||
|
|
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 |
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?
Quote:
|
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 |
Best regards
Lars P. Magnussen
#4
| |||
| |||
|
|
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 |
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.
Quote:
--
Bob Quintal
PA is y I've altered my email address.
#5
| |||
| |||
|
|
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 |
",'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.
#6
| |||
| |||
|
|
Yes, think I must do that to generate the lists and maybe afterwards remove the extra zeroes in production material. |
appear at all in the reports:
select .... RefField, val(mid$(RefField,2)) as SortField, ... order by
SortField, RefField
That takes also care of the situations where you have C23A and C23B.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.