Using Not Like

[petepro]

Using Access 2000

I have two tables that I want to compare one of the fiields and I want the
output to show me where the fields are different. I have a common field that
I can join the table on. This partially works for what I want to accomplish:

Diff: IIf([property].[owner] Not Like "*" & [horry county
property].[ownername] & "*","DIFF",Null)

I am creating a field call DIiff that will display if the fields in the two
tables are different. My problem is that it shows some as being different
when they are basically the same but just words are in different order. For
example:

Property.owner = Pete Provencher
Horry county property.ownername = Provencher Pete

In this case I don't wnat this to be displayed as diff.

Is there a way to do this?

Thanks in advance

Pete Provencher

 

[Tom Ellison]

Dear Pete:

The LIKE operator, used as you have here, looks for the exact, complete
string in owner withing the other string. It does not look at it one word
at a time without regard to the order of the words, as I believe you want.

You could write a VBA function to perform the methods you desire. You would
need to parse the owner string into words, using whatever methods you
define, and then look for those words, perhaps delimiting them as well,
within the target string.

For example, searching for "now is the time" within the string "what time is
it now, the man asked" should succeed I suspect, while searching for that
within "the timed nail is now" should fail, right? So you must delimit
whole words and ignore punctuation. Is that what you want? And, if the
string being searched contains a certain word only once, but the searching
string contains that word twice, is tat success or failure? You'll need to
define what succeeds and what fails quite thoroughly.

Tom Ellison

 

[John Nurick]

Maybe a function like this in a query:


Function ListsMatchOK(First As Variant, Second As Variant) As Boolean

Dim Dict As Object 'Scripting.Dictionary
Dim arSecond As Variant
Dim varItem As Variant

'Handle Null arguments for easy use in queries
If IsNull(First) Or IsNull(Second) Then
ListsMatchOK = False
Exit Function
End If

'Assumes First and Second are space-delimited lists of words.
'If necessary use Replace() or something to make them so.

'Convert first list into dictionary
Set Dict = CreateObject("Scripting.Dictionary")
For Each varItem In Split(First, " ")
Dict.Add varItem, True
Next

'Convert second list into array
arSecond = Split(Second, " ")

If Dict.Count = UBound(arSecond) + 1 Then
'both lists have same number of items
ListsMatchOK = True
'look up each item in second list
For Each varItem In arSecond
If Not Dict.Exists(varItem) Then
ListsMatchOK = False
End If
Next
Else
'lists have different number of items
ListsMatchOK = False
End If

End Function

Using Access 2000

I have two tables that I want to compare one of the fiields and I want the
output to show me where the fields are different. I have a common field that
I can join the table on. This partially works for what I want to accomplish:

Diff: IIf([property].[owner] Not Like "*" & [horry county
property].[ownername] & "*","DIFF",Null)

I am creating a field call DIiff that will display if the fields in the two
tables are different. My problem is that it shows some as being different
when they are basically the same but just words are in different order. For
example:

Property.owner = Pete Provencher
Horry county property.ownername = Provencher Pete

In this case I don't wnat this to be displayed as diff.

Is there a way to do this?

Thanks in advance

Pete Provencher

 

[petepro]

THank you both for your replies. I'll try the function that you wrote. This
might be a little beyond my knowledge but I'll give it a try.

Thanks again.

Pete

Maybe a function like this in a query:


Function ListsMatchOK(First As Variant, Second As Variant) As Boolean

Dim Dict As Object 'Scripting.Dictionary
Dim arSecond As Variant
Dim varItem As Variant

'Handle Null arguments for easy use in queries
If IsNull(First) Or IsNull(Second) Then
ListsMatchOK = False
Exit Function
End If

'Assumes First and Second are space-delimited lists of words.
'If necessary use Replace() or something to make them so.

'Convert first list into dictionary
Set Dict = CreateObject("Scripting.Dictionary")
For Each varItem In Split(First, " ")
Dict.Add varItem, True
Next

'Convert second list into array
arSecond = Split(Second, " ")

If Dict.Count = UBound(arSecond) + 1 Then
'both lists have same number of items
ListsMatchOK = True
'look up each item in second list
For Each varItem In arSecond
If Not Dict.Exists(varItem) Then
ListsMatchOK = False
End If
Next
Else
'lists have different number of items
ListsMatchOK = False
End If

End Function

Using Access 2000

I have two tables that I want to compare one of the fiields and I want the
output to show me where the fields are different. I have a common field
that
I can join the table on. This partially works for what I want to
accomplish:

Diff: IIf([property].[owner] Not Like "*" & [horry county
property].[ownername] & "*","DIFF",Null)

I am creating a field call DIiff that will display if the fields in the
two
tables are different. My problem is that it shows some as being different
when they are basically the same but just words are in different order.
For
example:

Property.owner = Pete Provencher
Horry county property.ownername = Provencher Pete

In this case I don't wnat this to be displayed as diff.

Is there a way to do this?

Thanks in advance

Pete Provencher

 

[John Nurick]

As Tom said, a lot depends on the precise rules. The code I posted
assumes that both strings contain the same words but in different
orders: e.g "Provencher Pete" matches "Pete Provencher" but not
"Provencher Peter" or "Peter J Provencher". It will also fail if there
are repeated words, e.g. "James James Morrison Morrison Weatherby George
Dupree".

THank you both for your replies. I'll try the function that you wrote. This
might be a little beyond my knowledge but I'll give it a try.

Thanks again.

Pete

Maybe a function like this in a query:


Function ListsMatchOK(First As Variant, Second As Variant) As Boolean

Dim Dict As Object 'Scripting.Dictionary
Dim arSecond As Variant
Dim varItem As Variant

'Handle Null arguments for easy use in queries
If IsNull(First) Or IsNull(Second) Then
ListsMatchOK = False
Exit Function
End If

'Assumes First and Second are space-delimited lists of words.
'If necessary use Replace() or something to make them so.

'Convert first list into dictionary
Set Dict = CreateObject("Scripting.Dictionary")
For Each varItem In Split(First, " ")
Dict.Add varItem, True
Next

'Convert second list into array
arSecond = Split(Second, " ")

If Dict.Count = UBound(arSecond) + 1 Then
'both lists have same number of items
ListsMatchOK = True
'look up each item in second list
For Each varItem In arSecond
If Not Dict.Exists(varItem) Then
ListsMatchOK = False
End If
Next
Else
'lists have different number of items
ListsMatchOK = False
End If

End Function

Using Access 2000

I have two tables that I want to compare one of the fiields and I want the
output to show me where the fields are different. I have a common field
that
I can join the table on. This partially works for what I want to
accomplish:

Diff: IIf([property].[owner] Not Like "*" & [horry county
property].[ownername] & "*","DIFF",Null)

I am creating a field call DIiff that will display if the fields in the
two
tables are different. My problem is that it shows some as being different
when they are basically the same but just words are in different order.
For
example:

Property.owner = Pete Provencher
Horry county property.ownername = Provencher Pete

In this case I don't wnat this to be displayed as diff.

Is there a way to do this?

Thanks in advance

Pete Provencher

 

[petepro]

My question is how do you rurn a function in a query. I looked up some
examples in a book and on the internet (not much help) and this is what I
did.

I created a query and put two fields from two differnet table: Owner from
Property table and Ownername from Horry County Property table. I created a
third column with the following:

Ownerinfo: ListsMatchOK([horry county property].[ownername],
[property].[owner])

When I run it I get the error Undefined function 'ListsMatchOK' in
expression.

I copied your Function to the Module Object.

I'm lost!!!!

Pete Provencer

As Tom said, a lot depends on the precise rules. The code I posted
assumes that both strings contain the same words but in different
orders: e.g "Provencher Pete" matches "Pete Provencher" but not
"Provencher Peter" or "Peter J Provencher". It will also fail if there
are repeated words, e.g. "James James Morrison Morrison Weatherby George
Dupree".

THank you both for your replies. I'll try the function that you wrote.
This
might be a little beyond my knowledge but I'll give it a try.

Thanks again.

Pete

Maybe a function like this in a query:


Function ListsMatchOK(First As Variant, Second As Variant) As Boolean

Dim Dict As Object 'Scripting.Dictionary
Dim arSecond As Variant
Dim varItem As Variant

'Handle Null arguments for easy use in queries
If IsNull(First) Or IsNull(Second) Then
ListsMatchOK = False
Exit Function
End If

'Assumes First and Second are space-delimited lists of words.
'If necessary use Replace() or something to make them so.

'Convert first list into dictionary
Set Dict = CreateObject("Scripting.Dictionary")
For Each varItem In Split(First, " ")
Dict.Add varItem, True
Next

'Convert second list into array
arSecond = Split(Second, " ")

If Dict.Count = UBound(arSecond) + 1 Then
'both lists have same number of items
ListsMatchOK = True
'look up each item in second list
For Each varItem In arSecond
If Not Dict.Exists(varItem) Then
ListsMatchOK = False
End If
Next
Else
'lists have different number of items
ListsMatchOK = False
End If

End Function


On Thu, 27 Apr 2006 17:24:25 -0400, "petepro"

Using Access 2000

I have two tables that I want to compare one of the fiields and I want
the
output to show me where the fields are different. I have a common field
that
I can join the table on. This partially works for what I want to
accomplish:

Diff: IIf([property].[owner] Not Like "*" & [horry county
property].[ownername] & "*","DIFF",Null)

I am creating a field call DIiff that will display if the fields in the
two
tables are different. My problem is that it shows some as being
different
when they are basically the same but just words are in different order.
For
example:

Property.owner = Pete Provencher
Horry county property.ownername = Provencher Pete

In this case I don't wnat this to be displayed as diff.

Is there a way to do this?

Thanks in advance

Pete Provencher

 

[John Nurick]

Hi Pete,

I'm not certain what you mean by "the Module Object". What you should
have done is

1) create a new standard module (as opposed to a class module or a
form's module)

2) make sure that
Option Explicit
appears at the start of the module, and type it in if it doesn't.

3) paste the function into the module after the Option declaration(s)

4) save the module with any name other than ListsMatchOK.

Then test the function by opening the Immediate Pane (Ctrl-G) and typing
e.g.

?ListsMatchOK("John Nurick", "Nurick John")

If it works there it should work in a query.

My question is how do you rurn a function in a query. I looked up some
examples in a book and on the internet (not much help) and this is what I
did.

I created a query and put two fields from two differnet table: Owner from
Property table and Ownername from Horry County Property table. I created a
third column with the following:

Ownerinfo: ListsMatchOK([horry county property].[ownername],
[property].[owner])

When I run it I get the error Undefined function 'ListsMatchOK' in
expression.

I copied your Function to the Module Object.

I'm lost!!!!

Pete Provencer

As Tom said, a lot depends on the precise rules. The code I posted
assumes that both strings contain the same words but in different
orders: e.g "Provencher Pete" matches "Pete Provencher" but not
"Provencher Peter" or "Peter J Provencher". It will also fail if there
are repeated words, e.g. "James James Morrison Morrison Weatherby George
Dupree".

THank you both for your replies. I'll try the function that you wrote.
This
might be a little beyond my knowledge but I'll give it a try.

Thanks again.

Pete
Maybe a function like this in a query:


Function ListsMatchOK(First As Variant, Second As Variant) As Boolean

Dim Dict As Object 'Scripting.Dictionary
Dim arSecond As Variant
Dim varItem As Variant

'Handle Null arguments for easy use in queries
If IsNull(First) Or IsNull(Second) Then
ListsMatchOK = False
Exit Function
End If

'Assumes First and Second are space-delimited lists of words.
'If necessary use Replace() or something to make them so.

'Convert first list into dictionary
Set Dict = CreateObject("Scripting.Dictionary")
For Each varItem In Split(First, " ")
Dict.Add varItem, True
Next

'Convert second list into array
arSecond = Split(Second, " ")

If Dict.Count = UBound(arSecond) + 1 Then
'both lists have same number of items
ListsMatchOK = True
'look up each item in second list
For Each varItem In arSecond
If Not Dict.Exists(varItem) Then
ListsMatchOK = False
End If
Next
Else
'lists have different number of items
ListsMatchOK = False
End If

End Function


On Thu, 27 Apr 2006 17:24:25 -0400, "petepro"

Using Access 2000

I have two tables that I want to compare one of the fiields and I want
the
output to show me where the fields are different. I have a common field
that
I can join the table on. This partially works for what I want to
accomplish:

Diff: IIf([property].[owner] Not Like "*" & [horry county
property].[ownername] & "*","DIFF",Null)

I am creating a field call DIiff that will display if the fields in the
two
tables are different. My problem is that it shows some as being
different
when they are basically the same but just words are in different order.
For
example:

Property.owner = Pete Provencher
Horry county property.ownername = Provencher Pete

In this case I don't wnat this to be displayed as diff.

Is there a way to do this?

Thanks in advance

Pete Provencher

 

[petepro]

I did the four steps and when I type ?ListsMatchOK("John Nurick", "Nurick
John") in the Immediate pane it came out True which appears to me that it
worked.

Now in the query design I put [horry county property].[ownername] in one
column, [property].[owner] in the second column, and ownerinfo:
ListsMatchOK([horry county property].[ownername],[property].[owner]) in the
third column.

When I run the query I get the following error:

Run time error 457 This key is already associated with an element of
this collection

When I click on Debug I see the following:

'Convert first list into dictionary
Set Dict = CreateObject("Scripting.Dictionary")
For Each varItem In Split(First, " ")
Dict.Add varItem, True
Next

Dict.Add varItem in Split(First, " ") is highlighted in yellow

Also in the query output which I can see if I clcik on End instead of Debug
I get a 0 in the Ownerinfo column.

Hope I was able to explain everything so you can understand.

Pete Provencher

Hi Pete,

I'm not certain what you mean by "the Module Object". What you should
have done is

1) create a new standard module (as opposed to a class module or a
form's module)

2) make sure that
Option Explicit
appears at the start of the module, and type it in if it doesn't.

3) paste the function into the module after the Option declaration(s)

4) save the module with any name other than ListsMatchOK.

Then test the function by opening the Immediate Pane (Ctrl-G) and typing
e.g.

?ListsMatchOK("John Nurick", "Nurick John")

If it works there it should work in a query.

My question is how do you rurn a function in a query. I looked up some
examples in a book and on the internet (not much help) and this is what I
did.

I created a query and put two fields from two differnet table: Owner from
Property table and Ownername from Horry County Property table. I created a
third column with the following:

Ownerinfo: ListsMatchOK([horry county property].[ownername],
[property].[owner])

When I run it I get the error Undefined function 'ListsMatchOK' in
expression.

I copied your Function to the Module Object.

I'm lost!!!!

Pete Provencer

As Tom said, a lot depends on the precise rules. The code I posted
assumes that both strings contain the same words but in different
orders: e.g "Provencher Pete" matches "Pete Provencher" but not
"Provencher Peter" or "Peter J Provencher". It will also fail if there
are repeated words, e.g. "James James Morrison Morrison Weatherby George
Dupree".

On Fri, 28 Apr 2006 10:37:29 -0400, "petepro"

THank you both for your replies. I'll try the function that you wrote.
This
might be a little beyond my knowledge but I'll give it a try.

Thanks again.

Pete
Maybe a function like this in a query:


Function ListsMatchOK(First As Variant, Second As Variant) As Boolean

Dim Dict As Object 'Scripting.Dictionary
Dim arSecond As Variant
Dim varItem As Variant

'Handle Null arguments for easy use in queries
If IsNull(First) Or IsNull(Second) Then
ListsMatchOK = False
Exit Function
End If

'Assumes First and Second are space-delimited lists of words.
'If necessary use Replace() or something to make them so.

'Convert first list into dictionary
Set Dict = CreateObject("Scripting.Dictionary")
For Each varItem In Split(First, " ")
Dict.Add varItem, True
Next

'Convert second list into array
arSecond = Split(Second, " ")

If Dict.Count = UBound(arSecond) + 1 Then
'both lists have same number of items
ListsMatchOK = True
'look up each item in second list
For Each varItem In arSecond
If Not Dict.Exists(varItem) Then
ListsMatchOK = False
End If
Next
Else
'lists have different number of items
ListsMatchOK = False
End If

End Function


On Thu, 27 Apr 2006 17:24:25 -0400, "petepro"

Using Access 2000

I have two tables that I want to compare one of the fiields and I want
the
output to show me where the fields are different. I have a common
field
that
I can join the table on. This partially works for what I want to
accomplish:

Diff: IIf([property].[owner] Not Like "*" & [horry county
property].[ownername] & "*","DIFF",Null)

I am creating a field call DIiff that will display if the fields in
the
two
tables are different. My problem is that it shows some as being
different
when they are basically the same but just words are in different
order.
For
example:

Property.owner = Pete Provencher
Horry county property.ownername = Provencher Pete

In this case I don't wnat this to be displayed as diff.

Is there a way to do this?

Thanks in advance

Pete Provencher

 

[John Nurick]

From one of my previous messages:

As Tom said, a lot depends on the precise rules. The code I posted
assumes that both strings contain the same words but in different
orders: e.g "Provencher Pete" matches "Pete Provencher" but not
"Provencher Peter" or "Peter J Provencher". It will also fail if there
are repeated words, e.g. "James James Morrison Morrison Weatherby George
Dupree".

In short, the function's behaving as expected. Error 457 is raised when
the
Dict.Add varItem, True
line of code attempts to add an item that's already in the dictionary;
this means that there's a repeated word (or term) in one of the fields.

The function assumes that the fields contain words (or maybe initials)
separated by spaces, and splits them on every space. So doubled spaces
matter:
Pete<space><space>Provencer
splits into three items:
Pete
<empty string>
Provencer

Writing code or queries to match disparate strings can be a very
difficult programming task and requires an understanding of both the
data and the rules to be applied to it. Can you be precise about these?

I did the four steps and when I type ?ListsMatchOK("John Nurick", "Nurick
John") in the Immediate pane it came out True which appears to me that it
worked.

Now in the query design I put [horry county property].[ownername] in one
column, [property].[owner] in the second column, and ownerinfo:
ListsMatchOK([horry county property].[ownername],[property].[owner]) in the
third column.

When I run the query I get the following error:

Run time error 457 This key is already associated with an element of
this collection

When I click on Debug I see the following:

'Convert first list into dictionary
Set Dict = CreateObject("Scripting.Dictionary")
For Each varItem In Split(First, " ")
Dict.Add varItem, True
Next

Dict.Add varItem in Split(First, " ") is highlighted in yellow

Also in the query output which I can see if I clcik on End instead of Debug
I get a 0 in the Ownerinfo column.

Hope I was able to explain everything so you can understand.

Pete Provencher

Hi Pete,

I'm not certain what you mean by "the Module Object". What you should
have done is

1) create a new standard module (as opposed to a class module or a
form's module)

2) make sure that
Option Explicit
appears at the start of the module, and type it in if it doesn't.

3) paste the function into the module after the Option declaration(s)

4) save the module with any name other than ListsMatchOK.

Then test the function by opening the Immediate Pane (Ctrl-G) and typing
e.g.

?ListsMatchOK("John Nurick", "Nurick John")

If it works there it should work in a query.

My question is how do you rurn a function in a query. I looked up some
examples in a book and on the internet (not much help) and this is what I
did.

I created a query and put two fields from two differnet table: Owner from
Property table and Ownername from Horry County Property table. I created a
third column with the following:

Ownerinfo: ListsMatchOK([horry county property].[ownername],
[property].[owner])

When I run it I get the error Undefined function 'ListsMatchOK' in
expression.

I copied your Function to the Module Object.

I'm lost!!!!

Pete Provencer
As Tom said, a lot depends on the precise rules. The code I posted
assumes that both strings contain the same words but in different
orders: e.g "Provencher Pete" matches "Pete Provencher" but not
"Provencher Peter" or "Peter J Provencher". It will also fail if there
are repeated words, e.g. "James James Morrison Morrison Weatherby George
Dupree".

On Fri, 28 Apr 2006 10:37:29 -0400, "petepro"

THank you both for your replies. I'll try the function that you wrote.
This
might be a little beyond my knowledge but I'll give it a try.

Thanks again.

Pete
Maybe a function like this in a query:


Function ListsMatchOK(First As Variant, Second As Variant) As Boolean

Dim Dict As Object 'Scripting.Dictionary
Dim arSecond As Variant
Dim varItem As Variant

'Handle Null arguments for easy use in queries
If IsNull(First) Or IsNull(Second) Then
ListsMatchOK = False
Exit Function
End If

'Assumes First and Second are space-delimited lists of words.
'If necessary use Replace() or something to make them so.

'Convert first list into dictionary
Set Dict = CreateObject("Scripting.Dictionary")
For Each varItem In Split(First, " ")
Dict.Add varItem, True
Next

'Convert second list into array
arSecond = Split(Second, " ")

If Dict.Count = UBound(arSecond) + 1 Then
'both lists have same number of items
ListsMatchOK = True
'look up each item in second list
For Each varItem In arSecond
If Not Dict.Exists(varItem) Then
ListsMatchOK = False
End If
Next
Else
'lists have different number of items
ListsMatchOK = False
End If

End Function


On Thu, 27 Apr 2006 17:24:25 -0400, "petepro"

Using Access 2000

I have two tables that I want to compare one of the fiields and I want
the
output to show me where the fields are different. I have a common
field
that
I can join the table on. This partially works for what I want to
accomplish:

Diff: IIf([property].[owner] Not Like "*" & [horry county
property].[ownername] & "*","DIFF",Null)

I am creating a field call DIiff that will display if the fields in
the
two
tables are different. My problem is that it shows some as being
different
when they are basically the same but just words are in different
order.
For
example:

Property.owner = Pete Provencher
Horry county property.ownername = Provencher Pete

In this case I don't wnat this to be displayed as diff.

 

[petepro]

I think you're starting to get into areas that I really have no knowledge
of. I don't expect you to figure out and write my code for me. I really
appreciate what you have done. I think I'll just check the records manually,
all 30,000 records. I'll paly eith the function that you sent and see if I
can figure it out. Thanks again for all of your help.

Pete Provencher

From one of my previous messages:

As Tom said, a lot depends on the precise rules. The code I posted
assumes that both strings contain the same words but in different
orders: e.g "Provencher Pete" matches "Pete Provencher" but not
"Provencher Peter" or "Peter J Provencher". It will also fail if there
are repeated words, e.g. "James James Morrison Morrison Weatherby George
Dupree".

In short, the function's behaving as expected. Error 457 is raised when
the
Dict.Add varItem, True
line of code attempts to add an item that's already in the dictionary;
this means that there's a repeated word (or term) in one of the fields.

The function assumes that the fields contain words (or maybe initials)
separated by spaces, and splits them on every space. So doubled spaces
matter:
Pete<space><space>Provencer
splits into three items:
Pete
<empty string>
Provencer

Writing code or queries to match disparate strings can be a very
difficult programming task and requires an understanding of both the
data and the rules to be applied to it. Can you be precise about these?

I did the four steps and when I type ?ListsMatchOK("John Nurick", "Nurick
John") in the Immediate pane it came out True which appears to me that it
worked.

Now in the query design I put [horry county property].[ownername] in one
column, [property].[owner] in the second column, and ownerinfo:
ListsMatchOK([horry county property].[ownername],[property].[owner]) in
the
third column.

When I run the query I get the following error:

Run time error 457 This key is already associated with an element of
this collection

When I click on Debug I see the following:

'Convert first list into dictionary
Set Dict = CreateObject("Scripting.Dictionary")
For Each varItem In Split(First, " ")
Dict.Add varItem, True
Next

Dict.Add varItem in Split(First, " ") is highlighted in yellow

Also in the query output which I can see if I clcik on End instead of
Debug
I get a 0 in the Ownerinfo column.

Hope I was able to explain everything so you can understand.

Pete Provencher

Hi Pete,

I'm not certain what you mean by "the Module Object". What you should
have done is

1) create a new standard module (as opposed to a class module or a
form's module)

2) make sure that
Option Explicit
appears at the start of the module, and type it in if it doesn't.

3) paste the function into the module after the Option declaration(s)

4) save the module with any name other than ListsMatchOK.

Then test the function by opening the Immediate Pane (Ctrl-G) and typing
e.g.

?ListsMatchOK("John Nurick", "Nurick John")

If it works there it should work in a query.

On Fri, 28 Apr 2006 16:46:36 -0400, "petepro"

My question is how do you rurn a function in a query. I looked up some
examples in a book and on the internet (not much help) and this is what
I
did.

I created a query and put two fields from two differnet table: Owner
from
Property table and Ownername from Horry County Property table. I created
a
third column with the following:

Ownerinfo: ListsMatchOK([horry county property].[ownername],
[property].[owner])

When I run it I get the error Undefined function 'ListsMatchOK' in
expression.

I copied your Function to the Module Object.

I'm lost!!!!

Pete Provencer
As Tom said, a lot depends on the precise rules. The code I posted
assumes that both strings contain the same words but in different
orders: e.g "Provencher Pete" matches "Pete Provencher" but not
"Provencher Peter" or "Peter J Provencher". It will also fail if there
are repeated words, e.g. "James James Morrison Morrison Weatherby
George
Dupree".

On Fri, 28 Apr 2006 10:37:29 -0400, "petepro"

THank you both for your replies. I'll try the function that you wrote.
This
might be a little beyond my knowledge but I'll give it a try.

Thanks again.

Pete
Maybe a function like this in a query:


Function ListsMatchOK(First As Variant, Second As Variant) As
Boolean

Dim Dict As Object 'Scripting.Dictionary
Dim arSecond As Variant
Dim varItem As Variant

'Handle Null arguments for easy use in queries
If IsNull(First) Or IsNull(Second) Then
ListsMatchOK = False
Exit Function
End If

'Assumes First and Second are space-delimited lists of words.
'If necessary use Replace() or something to make them so.

'Convert first list into dictionary
Set Dict = CreateObject("Scripting.Dictionary")
For Each varItem In Split(First, " ")
Dict.Add varItem, True
Next

'Convert second list into array
arSecond = Split(Second, " ")

If Dict.Count = UBound(arSecond) + 1 Then
'both lists have same number of items
ListsMatchOK = True
'look up each item in second list
For Each varItem In arSecond
If Not Dict.Exists(varItem) Then
ListsMatchOK = False
End If
Next
Else
'lists have different number of items
ListsMatchOK = False
End If

End Function


On Thu, 27 Apr 2006 17:24:25 -0400, "petepro"

Using Access 2000

I have two tables that I want to compare one of the fiields and I
want
the
output to show me where the fields are different. I have a common
field
that
I can join the table on. This partially works for what I want to
accomplish:

Diff: IIf([property].[owner] Not Like "*" & [horry county
property].[ownername] & "*","DIFF",Null)

I am creating a field call DIiff that will display if the fields in
the
two
tables are different. My problem is that it shows some as being
different
when they are basically the same but just words are in different
order.
For
example:

Property.owner = Pete Provencher
Horry county property.ownername = Provencher Pete

In this case I don't wnat this to be displayed as diff.

 

[John Nurick]

When you get started you'll probably find that some pretty simple rules
will cover matching 90% or more of the records. Post back here when
you've figured them out, with examples of the data, and we should be
able to help.

I think you're starting to get into areas that I really have no knowledge
of. I don't expect you to figure out and write my code for me. I really
appreciate what you have done. I think I'll just check the records manually,
all 30,000 records. I'll paly eith the function that you sent and see if I
can figure it out. Thanks again for all of your help.

Pete Provencher

From one of my previous messages:

As Tom said, a lot depends on the precise rules. The code I posted
assumes that both strings contain the same words but in different
orders: e.g "Provencher Pete" matches "Pete Provencher" but not
"Provencher Peter" or "Peter J Provencher". It will also fail if there
are repeated words, e.g. "James James Morrison Morrison Weatherby George
Dupree".

In short, the function's behaving as expected. Error 457 is raised when
the
Dict.Add varItem, True
line of code attempts to add an item that's already in the dictionary;
this means that there's a repeated word (or term) in one of the fields.

The function assumes that the fields contain words (or maybe initials)
separated by spaces, and splits them on every space. So doubled spaces
matter:
Pete<space><space>Provencer
splits into three items:
Pete
<empty string>
Provencer

Writing code or queries to match disparate strings can be a very
difficult programming task and requires an understanding of both the
data and the rules to be applied to it. Can you be precise about these?

I did the four steps and when I type ?ListsMatchOK("John Nurick", "Nurick
John") in the Immediate pane it came out True which appears to me that it
worked.

Now in the query design I put [horry county property].[ownername] in one
column, [property].[owner] in the second column, and ownerinfo:
ListsMatchOK([horry county property].[ownername],[property].[owner]) in
the
third column.

When I run the query I get the following error:

Run time error 457 This key is already associated with an element of
this collection

When I click on Debug I see the following:

'Convert first list into dictionary
Set Dict = CreateObject("Scripting.Dictionary")
For Each varItem In Split(First, " ")
Dict.Add varItem, True
Next

Dict.Add varItem in Split(First, " ") is highlighted in yellow

Also in the query output which I can see if I clcik on End instead of
Debug
I get a 0 in the Ownerinfo column.

Hope I was able to explain everything so you can understand.

Pete Provencher
Hi Pete,

I'm not certain what you mean by "the Module Object". What you should
have done is

1) create a new standard module (as opposed to a class module or a
form's module)

2) make sure that
Option Explicit
appears at the start of the module, and type it in if it doesn't.

3) paste the function into the module after the Option declaration(s)

4) save the module with any name other than ListsMatchOK.

Then test the function by opening the Immediate Pane (Ctrl-G) and typing
e.g.

?ListsMatchOK("John Nurick", "Nurick John")

If it works there it should work in a query.

On Fri, 28 Apr 2006 16:46:36 -0400, "petepro"

My question is how do you rurn a function in a query. I looked up some
examples in a book and on the internet (not much help) and this is what
I
did.

I created a query and put two fields from two differnet table: Owner
from
Property table and Ownername from Horry County Property table. I created
a
third column with the following:

Ownerinfo: ListsMatchOK([horry county property].[ownername],
[property].[owner])

When I run it I get the error Undefined function 'ListsMatchOK' in
expression.

I copied your Function to the Module Object.

I'm lost!!!!

Pete Provencer
As Tom said, a lot depends on the precise rules. The code I posted
assumes that both strings contain the same words but in different
orders: e.g "Provencher Pete" matches "Pete Provencher" but not
"Provencher Peter" or "Peter J Provencher". It will also fail if there
are repeated words, e.g. "James James Morrison Morrison Weatherby
George
Dupree".

On Fri, 28 Apr 2006 10:37:29 -0400, "petepro"

THank you both for your replies. I'll try the function that you wrote.
This
might be a little beyond my knowledge but I'll give it a try.

Thanks again.

Pete
Maybe a function like this in a query:


Function ListsMatchOK(First As Variant, Second As Variant) As
Boolean

Dim Dict As Object 'Scripting.Dictionary
Dim arSecond As Variant
Dim varItem As Variant

'Handle Null arguments for easy use in queries
If IsNull(First) Or IsNull(Second) Then
ListsMatchOK = False
Exit Function
End If

'Assumes First and Second are space-delimited lists of words.
'If necessary use Replace() or something to make them so.

'Convert first list into dictionary
Set Dict = CreateObject("Scripting.Dictionary")
For Each varItem In Split(First, " ")
Dict.Add varItem, True
Next

'Convert second list into array
arSecond = Split(Second, " ")

If Dict.Count = UBound(arSecond) + 1 Then
'both lists have same number of items
ListsMatchOK = True
'look up each item in second list
For Each varItem In arSecond
If Not Dict.Exists(varItem) Then
ListsMatchOK = False
End If
Next
Else
'lists have different number of items
ListsMatchOK = False
End If

End Function


On Thu, 27 Apr 2006 17:24:25 -0400, "petepro"

Using Access 2000

I have two tables that I want to compare one of the fiields and I
want
the
output to show me where the fields are different. I have a common
field
that
I can join the table on. This partially works for what I want to
accomplish:

Diff: IIf([property].[owner] Not Like "*" & [horry county
property].[ownername] & "*","DIFF",Null)

I am creating a field call DIiff that will display if the fields in
the
two
tables are different. My problem is that it shows some as being
different
when they are basically the same but just words are in different
order.
For
example:

Property.owner = Pete Provencher
Horry county property.ownername = Provencher Pete

In this case I don't wnat this to be displayed as diff.