Finding Codes with Instr.. (1 Viewer)

[SteveE]

I have a large number of 4 digit codes which describe a drop which can be applied to a route, the route is made up of the combination. I am look for a way to check the codes against a table.
"A001/A901/C002/V004" could be the chosen string code BUT they may be entered as "C002/V004/A901/B001" etc

If I could be certain that they would always be entered in the correct format a straight forward lookup would do, but this is downloaded from an outside source and not always true....

I think I need an instr() type function combined with a loop but not sure how to construct, or if that is the correct method.
I need the lookup to match "C002/V004/A901/B001" to the stored code of "A001/A901/C002/V004"
table
Code
A001
A901
C002
V004
etc

Any advise welcome

 

[pr2-eugin]

Is this code stored as one long String "A001/A901/C002/V004" in a field in the table? So the data in the table is like..

A001/A901/C002/V004
B001/B901/D002/W004
C001/C901/E002/X004
:
:

Or the data in the table is like..

A001	
A901	
C002    
V004
B001	
B901	
D002    
W004
:

I am also a bit confused what you are trying to do, could you give another shot at explaining ??

 

[CJ_London]

Here is a bit of code you could adapt to your requirements. This looks so see if any element of the sourcelist is in the targetlist whereas you are looking for all elements of the sourcelist are in the targetlist

Function IsInList(SourceList, TargetList As String, TestType As Integer) As Integer '0 for numbers, 1 for strings
Dim SourceRow, TargetRow As Integer
Dim SourceArray() As String
Dim TargetArray() As String
    ' determine if any item in the source list is in the target list
    
    IsInList = 0
    If TargetList = "All" Then
        IsInList = 1
        Exit Function
    End If
    If Len(SourceList) = 0 Or IsNull(SourceList) Then
        IsInList = 0
        Exit Function
    Else
        TargetArray = Split(TargetList, ",")
        SourceArray = Split(SourceList, ",")
        For SourceRow = 0 To UBound(SourceArray())
            For TargetRow = 0 To UBound(TargetArray())
                Select Case TestType
                    Case 0
                        If TargetArray(TargetRow) = SourceArray(SourceRow) Then
                            IsInList = 1
                            Exit Function
                        End If
                    Case 1
                        If InStr(SourceArray(SourceRow), TargetArray(TargetRow)) > 0 Then
                            IsInList = 1
                            Exit Function
                        End If
                End Select
            Next TargetRow
        Next SourceRow
    End If
    
End Function

 

[SteveE]

thanks

The routes are a combiniation of deliveries could be Drop A with B with C etc.
The Drop Table has indivisual A B C but also the combinations as a string i.e. A/B/C/D or A/B/C so trying to find the correct table code for a combiniation string. I knw it unclear as Im trying to explain and confusing myself !!

 

[SteveE]

Thank CJ I will take it away and let you know if I succeed

 

[Brianwarnock]

thanks

The routes are a combiniation of deliveries could be Drop A with B with C etc.
The Drop Table has indivisual A B C but also the combinations as a string i.e. A/B/C/D or A/B/C so trying to find the correct table code for a combiniation string. I knw it unclear as Im trying to explain and confusing myself !!

If the combinations are always in alpha sort order then you could look to sort all cases that are Len > 4 and then do a simple lookup.

Sorting arrays is covered in many places, this one looks comprehensive,

http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/830b42cf-8c97-4aaf-b34b-d860773281f7/

I would try to convert the sub to a function and do my split there and a join to create my new string after the sort.

Brian

 

[jdraw]

steveE,

Could you post a copy of your tables and relationships as a jpg?
My guess, and it is a guess only at this time, is that you may have a table structure issue.

I "hear" routes and possible stops.

 

[SteveE]

This is snapshot jpeg of the look-up table, Each Route is coded with a store code , when more than 1 store on a run the code is R### / R### / R### etc

The Carrier will submit charging using the store code R### / R### etc depending on the actual run on the day. What I am trying to achieve is a look-up of the carrier submitted code against the look-up table to retrieve the agreed charge.

Instructions Come in mixed order as the run maybe completed in a different order but the charge should be the same

 

[gemma-the-husky]

assuming the coded string is the actual drop route, and is significant, i would be inclined to store two strings.

a) the original string, and
b) the same string sorted.

now you can compare the sorted strings, while having the actual order available. although this is duplication, the effort of resorting strings would be avoided, which I expect is preferable.

note that the best way of storing the actual drops would be in a subtable, rather than in the current format of the string, although that would not simplify route comparison.

 

[SteveE]

Thank you Gemma I thing I will go down the line of sorting the codes into a single new field, then using a lookup. Could you advise the best way of actually sorting the codes in a query / function aim is to have R393 / R252 / R334 or R393 / R252 / R393 sorted as R252/R334/R393 (removing spaces etc).

thanks all

Steve

 

[Brianwarnock]

Steve why are you asking Dave the question I gave you the answer to, did you look at the link.

I decided to do it for you , the function below , which is the bubblesort Sub in the link, modified, will take in a string and return it sorted.

Brian

Function Farraysort(mystr As String) As String

Dim strTemp As String
  Dim i As Long
  Dim j As Long
  Dim lngMin As Long
  Dim lngMax As Long

arr = Split(mystr, "/")
  lngMin = LBound(arr)
  lngMax = UBound(arr)
  For i = lngMin To lngMax - 1
    For j = i + 1 To lngMax
      If arr(i) > arr(j) Then
        strTemp = arr(i)
        arr(i) = arr(j)
        arr(j) = strTemp
      End If
    Next j
  Next i
  
  Farraysort = Join(arr, "/")
  
End Function

 

[SteveE]

Thanks Brian works like a dream, sorry I did look at the link you sent but got "way-laid" on something else and I picked up Daves reply today.
thanks again
Steve

 

[Brianwarnock]

Great sorry if I sounded a bit sharp but another poster had p****d me off, you know how uptight we scousers get.

Brian