How do you parse a modified number 1

[EckyThump]

If I have a table of part numbers that have been modified such as:
2-1234-001
2-1234-002
2-1234-003
3-444445-009
3-444445-010
How do I turn that into a table that shows:
Part Qty
2-1234 3
3-444445 2
I think its a right trim sort of problem.
Right(PartNo,Len(PartNo - (Number of digits including the first"-" from right))).
The Modifying digits may not always be 3, perhaps 4448-9976, so it may not always be just the last four digits.
Something like that, or am I barking up the wrong tree?
Thanks.

 

[TomCologne]

How about:

Trim(Left(PartNo,InStrRev(PartNo,"-")-1)}

Note that InStrRev is only available through a workaround in Acc2k.


TomCologne

 

[EckyThump]

Thats great many thanks,
I have 2003
but some users may have Access 2000, whats the work around?

 

[TomCologne]

ACC2000: Cannot Use New Visual Basic for Application Functions as Expressions

http://support.microsoft.com/kb/225956/en-us

TomCologne

 

[TomCologne]

BTW, Trim isn't actually necessary, it's more of an extra precaution.

TomCologne

 

[EckyThump]

Thats great, but there is a problem with my logic:
some of the numbers have no modifier:
6-555432
in which case I want to keep those but using the expression you gave its obviously trimming where it shouldnt.
Any ideas?

 

[Remou]

How about:

[tt]IIf(Len(PartNo)-Len(Replace(PartNo,"-",""))=2,Left(PartNo,InStrRev(PartNo,"-")-1),PartNo)[/tt]

 

[EckyThump]

Its a trickier problem than first appeared. it involves a large number of varied part numbers in many different formats, EG:
1. 6-2211334
2. 6-6675444-001
3. 6-6675444-002
4. 3456-67543
5. 3456-67543-01
6. 3456-67543-02

I think your trim statement would work if could first I check to see if the number of "-" characters in the string was geater than 1. Is this possible?

 

[TomCologne]

Sorry, I spaced that...

Something like this:

IIf([PartNo] Like "*" & "-" & "*" & "-" & "*",Left([PartNo],InStrRev([PartNo],"-")-1),[PartNo])

should do it.

Thanks for the star,

TomCologne

 

[Remou]

See my post.

 

[TomCologne]

Remou,

Your response came after my last refresh...,

TomCologne

 

[Remou]

Me too, "see my post" was in response to EckyThump!

 

[EckyThump]

Great
I have got something of a start in what I am trying to achieve. Sometimes a few pointers in the right direction is all it takes to get your steam up and get up and running.
Thanks for you time and wisdom guys!!