Re: string manipulation
From: David Kennedy (dkennedy85_at_hotmail.com)
Date: 12/21/04
- Next message: Irshad Alam: "Selection of a Printer"
- Previous message: Henry: "declare 'arByte() As Byte' as 'Private Const' ?"
- In reply to: John Nurick: "Re: string manipulation"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 21 Dec 2004 09:08:19 -0000
John,
Again apologies for not making sense, I had a look at the code you sent and
I think I can use
it,If I play around with it I think I can get a result.
to answer your questions:
What does it mean when both numbers have the same number of digits?
If for example the MfrPN was 555....999 then it would mean part number 555
and part number 999
And what do the various numbers of dots mean? you have .. ... .... in
different places
The dots have no meaning,its just the way the data was entered,using the
above example
it could have been inputted say 555/999 or 555,999
Can one of these encoded values in MfrPN represent more than two distinct
part numbers
(e.g. a range of numbers)?
The answer here is no, that was the first question I asked when I recieved
this database
Sorry again for not painting a better picture ,i`ll try your code and see
what results I get.
Thanks for your time and patience
Regards
David K
"John Nurick" <j.mapSoN.nurick@dial.pipex.com> a écrit dans le message de
news:t2bes0lfci6na6kh2ei9j64upo4n59nmrk@4ax.com...
>
> 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.
- Next message: Irshad Alam: "Selection of a Printer"
- Previous message: Henry: "declare 'arByte() As Byte' as 'Private Const' ?"
- In reply to: John Nurick: "Re: string manipulation"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
