Formatting Address Fields

[Michael Bartels]

Hi everyone,
Can someone help me with this...

I have 2 tables and am trying to build a query that will
return the duplicate records. The criteria is for both
records to have the same Address, City, State, ZIP.
City, State, and ZIP are OK, the problem is in my address
field: the format is not homogeneous (10 Main, 10 Main
St., 10 Main Street). In other words the address is not
EXACTLY the same in both tables even though it IS the
same.

SELECT [Table1].Address, [Table1].City, [Table1].State,
[Tabel1].ZIP
FROM [Table1] INNER JOIN Table ON
([Table1].Address=Table2.Address) AND
([Table1].City=Table2.City);

Any ideas on how to modify this? Ideally I would like for
the St, St., Street, etc. to be ignored all toghether in
the Query, or for #, name of street, and "street" to be
in three separate fields.

Any insight at all would be awesome!!!
Thanks

 

[frank stone]

instead of table1.address = table2.address, try changing
the = operator to the like operator.

 

[Guest]

I tryied, but I just get the same set of data...
Anything else I could try??

Thank you!

-----Original Message-----
instead of table1.address = table2.address, try changing
the = operator to the like operator.

-----Original Message-----
Hi everyone,
Can someone help me with this...

I have 2 tables and am trying to build a query that will
return the duplicate records. The criteria is for both
records to have the same Address, City, State, ZIP.
City, State, and ZIP are OK, the problem is in my address
field: the format is not homogeneous (10 Main, 10 Main
St., 10 Main Street). In other words the address is not
EXACTLY the same in both tables even though it IS the
same.

SELECT [Table1].Address, [Table1].City, [Table1].State,
[Tabel1].ZIP
FROM [Table1] INNER JOIN Table ON
([Table1].Address=Table2.Address) AND
([Table1].City=Table2.City);

Any ideas on how to modify this? Ideally I would like for
the St, St., Street, etc. to be ignored all toghether in
the Query, or for #, name of street, and "street" to be
in three separate fields.

Any insight at all would be awesome!!!
Thanks
.

.

 

[Gary Walter]

Hi Michael,

Start a new Module.

Copy and Paste the following code:

Option Explicit
Public Function fNthElementStatic(pKeyString As Variant, _
Delimiter As String, _
ByVal ElementNo As Integer) As Variant
On Error GoTo Err_fNthElementStatic
Static arrSegments As Variant
Static KeyString As Variant

If pKeyString = KeyString Then
'same string
Else
'pKeyString is new or is null
KeyString = pKeyString
If Len(Trim(KeyString & "")) > 0 Then
'pKeyString was not Null nor ZLS
arrSegments = Split(KeyString, Delimiter, -1, vbTextCompare)
Else
'pKeyString was Null or ZLS
fNthElementStatic = Null
Exit Function
End If

End If

If ((ElementNo - 1) <= UBound(arrSegments)) _
And (ElementNo > 0) Then
fNthElementStatic = arrSegments(ElementNo - 1)
Else
fNthElementStatic = Null
End If
Exit_fNthElementStatic:
Exit Function

Err_fNthElementStatic:
MsgBox Err.Description
Resume Exit_fNthElementStatic
End Function
'***end of code****

Save the module as "modUtility"

Click on Debug/Compile..

If no compile errors, then....

I might add 3 text fields to each table if you can

Add1 Add2 Add3

Then run an update query on your 2 tables:

UPDATE Table1
SET
[Add1] = fNthElementStatic([Address]," ",1),
[Add2] = fNthElementStatic([Address]," ",2),
[Add3] = fNthElementStatic([Address]," ",3);

then

UPDATE Table
SET
[Add1] = fNthElementStatic([Address]," ",1),
[Add2] = fNthElementStatic([Address]," ",2),
[Add3] = fNthElementStatic([Address]," ",3);

Then your data would determine which fields
you can match on to find a "duplicate."

Not perfect...but better.

Good luck,

Gary Walter

I tryied, but I just get the same set of data...
Anything else I could try??

Thank you!

-----Original Message-----
instead of table1.address = table2.address, try changing
the = operator to the like operator.

-----Original Message-----
Hi everyone,
Can someone help me with this...

I have 2 tables and am trying to build a query that will
return the duplicate records. The criteria is for both
records to have the same Address, City, State, ZIP.
City, State, and ZIP are OK, the problem is in my address
field: the format is not homogeneous (10 Main, 10 Main
St., 10 Main Street). In other words the address is not
EXACTLY the same in both tables even though it IS the
same.

SELECT [Table1].Address, [Table1].City, [Table1].State,
[Tabel1].ZIP
FROM [Table1] INNER JOIN Table ON
([Table1].Address=Table2.Address) AND
([Table1].City=Table2.City);

Any ideas on how to modify this? Ideally I would like for
the St, St., Street, etc. to be ignored all toghether in
the Query, or for #, name of street, and "street" to be
in three separate fields.

Any insight at all would be awesome!!!
Thanks
.

.