string manipulation

  • Thread starter Thread starter David Kennedy
  • Start date Start date
D

David Kennedy

Hi,
I have a table with 3 fields(manufacturer name,part number and competitor
part number)
and over 50000 records for example:

MfrName MfrPN CompPN
AA 123..01 X231
AA A456 C999
AA 101 X231
AA 12....999 OA88
AA 999 OA88

-the first MfrPN has 123..01 and its CompPN is X231
this means that both 123 and 101 are the same as ComPN X231
-however the third record shows that 101 is the same as ComPN X231
-the same applies for MfrPN 12..999

-What I need is a piece of code to chop off the ..01 ....999 or whatever
if the number is found elsewhere in the table and has the same CompPN
-there may be a case where the ..01 etc doesnt exist and will need to be
added
to the table.

Can someone help me with this?
any help would be greatly appreciated
Thanks
David K
 
David,

I'm not real clear about what you're trying to do, but here's a function you
could use to lop off the dot dot number part of a string. You could call
this function or some variant of it from a routine that loops through the
part number fields of your table, processes the contents and then updates the
record where a dot dot number was found.

Public Function fncStringManipulation(strPartNumber As String) As String
Dim intFirstDotPosition As Integer

intFirstDotPosition = InStr(strPartNumber, ".")
fncStringManipulation = Left(strPartNumber, (intFirstDotPosition - 1))
Debug.Print fncStringManipulation

End Function


Good luck,

Ken
 
Hi David,

You'll have to explain a bit more carefully. How is it that "..01"
matches "101" while "....999" matches "999"? Is the data below a fair
sample of your actual data, or is it something you've just made up (and
therefore - the regulars here have discovered - likely to be
misleading)?
 
John,

Sorry I didnt get to reply to you sooner I have been away for a few days.
The data is a fair sample of the actual data.But now that I read back my
description
it sounds like gibberish.

take the MfrPN 123..01 - this means two part numbers-> 123 and 101
the 101 is got by chopping the 23 off and appending the 01 to the 1

as for 12....999 - this means part numbers 12 and 999
if the length of the numbers after the dots is greater than the length of
the numbers
before the dots then no chopping off or appending is required

other examples:
1223334099111...222 means part numbers 1223334099111 and 1223334099222

9999..222222 means part numbers 9999 and 222222

I hope Im making a bit more sense
Thanks for your reply
David K
 
It's becoming less unclear. What does it mean when both numbers have the
same number of digits? And what do the various numbers of dots mean? you
have .. ... .... in different places. Can one of these encoded values
in MfrPN represent more than two distinct part numbers (e.g. a range of
numbers)?

The general approach would probably be to write a little custom function
that takes a string like this

digits dots digits

and returns the second part number. The rest (as I understand it) can be
done in SQL. The function could be as simple as the air code below - but
I doubt it as I suspect that you still haven't revealed all the rules.


Public Function SecondPN(V As Variant) As Variant
Dim strAll As String
Dim strFirst As String
Dim strSecond As String
Dim j As Long

SecondPN = Null
If IsNull(V) Then
Exit Function
End If
strAll = CStr(V)
If InStr(strAll, "..") = 0 Then
'no dots
Exit Function
End If

j = 1
Do While Mid(strAll, j, 1) Like "#"
strFirst = strFirst & Mid(strAll, j, 1)
j = j + 1
Loop
Do While Mid(strAll, j, 1) = "."
j = j + 1
Loop
strSecond = Mid(strAll, j)

If Len(strSecond) >= Len(strFirst) Then
SecondPN = strSecond
Else
SecondPN = Left(strFirst, 1) & strSecond
End If

End Function




John,

Sorry I didnt get to reply to you sooner I have been away for a few days.
The data is a fair sample of the actual data.But now that I read back my
description
it sounds like gibberish.

take the MfrPN 123..01 - this means two part numbers-> 123 and 101
the 101 is got by chopping the 23 off and appending the 01 to the 1

as for 12....999 - this means part numbers 12 and 999
if the length of the numbers after the dots is greater than the length of
the numbers
before the dots then no chopping off or appending is required

other examples:
1223334099111...222 means part numbers 1223334099111 and 1223334099222

9999..222222 means part numbers 9999 and 222222

I hope Im making a bit more sense
Thanks for your reply
David K
Click to expand...
 
Back
Top