Sort an alpha prefixed field numerically, then concatenate records

[drewkeller]

I've done a bit of searching and can't find anything that matches what I need to do. One table is a list of parent assemblies, a second table are the BOMs for the assemblies, and a third table shows the reference designators for each component in the BOM. I ask for the parent assembly number and then I have some query objects set up to give results for that specific parent assembly.

BOM Query result (PartID, Qty)
161-0001, 3
161-0002, 2

RefDes Query result (PartID, RefDes)
161-0001, R1
161-0001, R3
161-0001, R12
161-0002, XXDR1
161-0002, XXDR2


In my report, I want to show the sorted list of reference designators (the part in quotes below) for a certain part number like this:
161-0001... "R1,3,12"
161-0002... "XXDR1,2"

I have found partial solutions:
1.

http://www.tek-tips.com/faqs.cfm?spid=701&newpid=701&sfid=4233

2.

http://archives.hwg.org/hwg-techniques/01c151bd$24397a60$d54a149a@rudy

Solution 1 results something like "R1,R12,R3" which is somewhat close, but has three problems:
The prefix letter repeats
The sort needs to be numerically based
I would like it to work on a query result instead of being dependant on a table (it gives me an error if i don't use an actual table)

Solution 2 may help with the first two problems of Solution 1. However, it is based on a single letter in front of the number, but I have a variable number of letters in the prefix.

So, what remains is, I'm not quite sure how to get these two solutions to work together and I don't know how to get around the error that happens when I try to run it on a query result instead of an actual table.

Any help would be appreciated. Hopefully I did OK explaining it... ended as a rather long question.
Thanks
-drew

 

[SteveR77]

Drew,

Is there any pattern to finding the R and XX values?

What truly needs to happen is you need to grab your alpha portion of the string and sort on it then convert the numeric portion and sort on it.

I believe an Instr function would be a step in the right direction but I'd need a little more information on how the reference numbers are set up in order to give you a solution I know will work.

Thanks,

Steve

 

[drewkeller]

Steve, Thanks for the reply.

I'm not sure how InStr() would help me. I thought perhaps something along the lines of this logic would work (nested IIf() could do the similar but less flexible):
i=0
While IsNumeric(Right(teststr,i+1))
Inc(i)

The prefix portion in practical use is up to 4 character positions. The numerical portion of the field in practical use is up to 3 character positions. The prefix for a certain part number should always be the same in a particular assembly (resistors should always be "R", capacitors "C", etc). My implementation of Solution 1 above has a result set for just one part number in the assembly, for example, all of the 10k resistors which might be referenced on the assembly as R1,2,3,31,62,105, etc. So...
A0 represents the practical minimum and
AAAA000 represents a practical maximum,
where "A" represents any character A to Z
and "0" represents any digit 0-9

However, either the prefix or the number *could* be more than the practical length and the prefix might not always be the same for a given part number. For simplicity's sake, i would say it is probably acceptable to ignore cases that are not in our practical realm, if we need to.

For an example, let's say we are looking at assembly 345-0001 which is a printed circuit board assembly. The BOM for this assembly has the following parts:
Part No 161-0001 (quantity 3) is a 10k resistor used in locations R1,3,12
Part No 161-0002 (quantity 2) is a 0k resistor (jumper) used in locations XXDR1,XXDR2
Part No 140-0001 (quantity 4) is a 2.2uF capacitor used in locations C1,5,8,32.

Does that answer your question?

 

[hkaing79]

Why not just split the fields to begin with? You can join the two fields later if needs be.

RefDes = RefDesType & RefDesNum

Else you can write a module to extract all the text and all the numbers and place them into those two fields.

For sorting the numbers, you'll need to convert the RefDesNum to numeric by applying something like

total=0
i=len(RefDesNum)-1
while i >= 0
total=total + Right(RefDesNum,i,1) * 10^i
i=i-1
wend