Validation rule for duplicate address entries

[Guest]

Either in my access table or a form based on that table i would like to
prevent duplicate address entries. I want the user to be stopped right after
they enter the street address if its duplicate. I am not setting the street
field with a primary key but have indexed it for no duplicates. What is
happening now is that when a duplicate street address (not including city,
state and zip which are separate fields) is entered, it is not flagged until
after the city, state a zip are entered. Is there a validation rule I can set
for this?

 

[Guest]

Use the Before Update event of the control on a form where you enter the
street address:

If Not IsNull(DLookup("[STREET_ADDRESS]", "MyTableName",
"[STREET_ADDRESS] = '" & Me.txtStreetAddress & "'") Then
MsgBox "Duplicate Street Address Encountered"
Cancel = True
End If

Now, with that being said, it isn't going to work 100%. That is because
there are too many different ways to put in the same address.

214 Main
214 Main Street
214 Main St.
214 Main St, Apt.101
214 Maine
214 Mane

This is an age old problem is street address matching in any computer
application regardless of platform or languate. The best you can do is make
each component of a street address a different field. It consists of:

PreFix
Number
Street
StreetType
Suffix
Unit

Building lookup tables for most of these works pretty well, for example
PreFixes are like NW, SW, North, South, etc. Suffixes are about the same.
Street types include Street, Avenue, Boulavard, Lane, Road, Way, Drive, Loop,
etc.

You might try seaching the USPS website to see if you can get any detailed
info.

 

[Guest]

Thank you for your quick, empathetic and detailed response, i will give this
a try.
vacation

Use the Before Update event of the control on a form where you enter the
street address:

If Not IsNull(DLookup("[STREET_ADDRESS]", "MyTableName",
"[STREET_ADDRESS] = '" & Me.txtStreetAddress & "'") Then
MsgBox "Duplicate Street Address Encountered"
Cancel = True
End If

Now, with that being said, it isn't going to work 100%. That is because
there are too many different ways to put in the same address.

214 Main
214 Main Street
214 Main St.
214 Main St, Apt.101
214 Maine
214 Mane

This is an age old problem is street address matching in any computer
application regardless of platform or languate. The best you can do is make
each component of a street address a different field. It consists of:

PreFix
Number
Street
StreetType
Suffix
Unit

Building lookup tables for most of these works pretty well, for example
PreFixes are like NW, SW, North, South, etc. Suffixes are about the same.
Street types include Street, Avenue, Boulavard, Lane, Road, Way, Drive, Loop,
etc.

You might try seaching the USPS website to see if you can get any detailed
info.

Either in my access table or a form based on that table i would like to
prevent duplicate address entries. I want the user to be stopped right after
they enter the street address if its duplicate. I am not setting the street
field with a primary key but have indexed it for no duplicates. What is
happening now is that when a duplicate street address (not including city,
state and zip which are separate fields) is entered, it is not flagged until
after the city, state a zip are entered. Is there a validation rule I can set
for this?

 

[Guest]

I am not familiar with expression building. Is this an expression or macro? I
copied and pasted it into the control box of the form but it says it needs to
be debugged or can't find the macro.

Use the Before Update event of the control on a form where you enter the
street address:

If Not IsNull(DLookup("[STREET_ADDRESS]", "MyTableName",
"[STREET_ADDRESS] = '" & Me.txtStreetAddress & "'") Then
MsgBox "Duplicate Street Address Encountered"
Cancel = True
End If

Now, with that being said, it isn't going to work 100%. That is because
there are too many different ways to put in the same address.

214 Main
214 Main Street
214 Main St.
214 Main St, Apt.101
214 Maine
214 Mane

This is an age old problem is street address matching in any computer
application regardless of platform or languate. The best you can do is make
each component of a street address a different field. It consists of:

PreFix
Number
Street
StreetType
Suffix
Unit

Building lookup tables for most of these works pretty well, for example
PreFixes are like NW, SW, North, South, etc. Suffixes are about the same.
Street types include Street, Avenue, Boulavard, Lane, Road, Way, Drive, Loop,
etc.

You might try seaching the USPS website to see if you can get any detailed
info.

Either in my access table or a form based on that table i would like to
prevent duplicate address entries. I want the user to be stopped right after
they enter the street address if its duplicate. I am not setting the street
field with a primary key but have indexed it for no duplicates. What is
happening now is that when a duplicate street address (not including city,
state and zip which are separate fields) is entered, it is not flagged until
after the city, state a zip are entered. Is there a validation rule I can set
for this?

 

[Ron2006]

I guess I have a more basic question.

An address is the sum of ALL of the fields: address line1, address line
2 , etc, city, State, zip

123 Main Street in Calamizo, OR is NOT a duplicate of 123 Main Street,
Los Angeles, CA.

Testing for duplicates in the 123 main steet part of the address is
going to cause problems.

If you have a second address line for Apartment, then you have the
problem of 123 Main Steet, Apt A vs 123 Main Street, Apt B.

Checking for duplicates should include ALL aspects of the address for
determining whether it is truely duplicate.

And then what if it is a "duplicate"? Does Mrs John Doe have to have
an address different from Mr. John Doe when they actually do reside at
the same address? Or for that matter, Mrs April Smith (The Doe's
married daughter) who is living at the same address?

Ron.

 

[onedaywhen]

I guess I have a more basic question.

Checking for duplicates should include ALL aspects of the address for
determining whether it is truely duplicate.

And then what if it is a "duplicate"? Does Mrs John Doe have to have
an address different from Mr. John Doe when they actually do reside at
the same address? Or for that matter, Mrs April Smith (The Doe's
married daughter) who is living at the same address?

I think your world view is different to mine. If I need to know whether
two addressable entities share the same address entity then this would
be determined external to the DBMS; once determined this 'fact' is
recorded in the DBMS. Putting address attributes into the database then
looking for coincidence may not be the best approach e.g. as pointed
out a single abbreviation can break the logic.

More often then not, I don't need to know whether two addressable
entities share the same address entity, therefore in the DBMS address
becomes an attribute of the addressable entities and duplicates are of
no concern.

Jamie.

--