Compare 2 Multi Valued Fields

[Damien55]

Hello I need some help. I have two fields in a table that have multiple values. Example:

Field A:

CT, CA, PA

Field B:

CT, CA

I want to compare the two fields and indicate that there is a match because in this example CT and CA are in both fields. I would like to create a function. I'm not sure if I would have to use something with like or create a loop. I'm a bit lost. Any help would be much appreciated.

Thanks!

 

[ted.martin]

Think about this logically. When doing this in your head, you 'concatenate' (join) the CT,CA,PA together and then check through this string with each element (CT,CA) of the other; drawing the appropriate conclusions.

Use the INSTR function to find the commas and then 2 characters left and 2 characters long is the first reference.

It will be some fiddly coding using LEFT and RIGHT but would be a way through this. Have go, one logical step at a time, you may surprise yourself.

 

[Damien55]

After thinking about it I came up to that conculsion but I am running into issue with the function I am writing. I get matches on everything and am not sure what is off.

If Left(SupplyLocation, 2) = Left(DemandLocation, 2) Or Left(SupplyLocation, 2) = Mid(DemandLocation, 5, 2) Or Left(SupplyLocation, 2) = Mid(DemandLocation, 9, 2) Or Left(SupplyLocation, 2) = Mid(DemandLocation, 13, 2) Or Left(SupplyLocation, 2) = Mid(DemandLocation, 17, 2) Then
GetLocationMatch = "Match"
End If
If Mid(SupplyLocation, 5, 2) = Left(DemandLocation, 2) Or Mid(SupplyLocation, 5, 2) = Mid(DemandLocation, 5, 2) Or Mid(SupplyLocation, 5, 2) = Mid(DemandLocation, 9, 2) Or Mid(SupplyLocation, 5, 2) = Mid(DemandLocation, 13, 2) Or Mid(SupplyLocation, 5, 2) = Mid(DemandLocation, 17, 2) Then
GetLocationMatch = "Match"
End If
If Mid(SupplyLocation, 9, 2) = Left(DemandLocation, 2) Or Mid(SupplyLocation, 9, 2) = Mid(DemandLocation, 5, 2) Then
GetLocationMatch = "Match"
End If

See Attachment on Results

 

[Galaxiom]

With your requirement to compare MVFs I would recommend you use a normal relational data structure with the values held as individual records. Then the query is much simpler.

 

[JHB]

Create a UDF, (User defined Function), use the function Split to split the field A's value into an Array at the commas.
Then create a loop to cycle through all the elements returned by the Split function, and use the function InStr to see if there is a match in field B.

 

[ted.martin]

Take look at this; 3 queries should give you what you are looking for.