Re: string manipulation

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: John Nurick (j.mapSoN.nurick_at_dial.pipex.com)
Date: 12/20/04 

Date: Mon, 20 Dec 2004 20:09:49 +0000

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

On Mon, 20 Dec 2004 15:54:22 -0000, "David Kennedy"
<dkennedy85@hotmail.com> wrote:

>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
>
>"John Nurick" <j.mapSoN.nurick@dial.pipex.com> a écrit dans le message de
>news:7it3s0lvuor69ug9edp4e9ugtl4jj6bb3o@4ax.com...
>> 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)?
>>
>> On Thu, 16 Dec 2004 12:33:01 -0000, "David Kennedy"
>> <dkennedy85@hotmail.com> wrote:
>>
>> >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
>> >
>>
>> --
>> John Nurick [Microsoft Access MVP]
>>
>> Please respond in the newgroup and not by email.
> 

--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.

Relevant Pages

  • Re: string manipulation
    ... What does it mean when both numbers have the same number of digits? ... The dots have no meaning,its just the way the data was entered,using the ... > Dim strAll As String ... > Dim strFirst As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Fuzzy Matching-Algorithmus in VBA
    ... Public strSecond As String ... Dim dist As Integer ... Dim cost As Integer ' cost ...
    (microsoft.public.de.word.vba)
  • Re: Fuzzy Matching-Algorithmus in VBA
    ... Public strSecond As String ... Dim mi As Long ... Dim cost As Long ' cost ...
    (microsoft.public.de.word.vba)
  • Re: Looking for a Fuzzy Matching algorithm
    ... Public strSecond As String ... Dim dist As Integer ... Dim cost As Integer ' cost ...
    (microsoft.public.word.vba.general)
  • Re: Looking for a Fuzzy Matching algorithm
    ... Public strSecond As String ... Dim dist As Integer ... Dim cost As Integer ' cost ...
    (microsoft.public.word.vba.general)