Duplicates in 1 field

[LHood]

Hi,

I am trying to remove duplicates from 1 field, for example in the field i have named as Address Line 1 the first record is "1 sample road, 1sampleroad, 1 Sample Road." then the next record is "2sampleroad, 2 Sample Road" then third record "3 Sample Road" which is how i would like it to look.

So its not every record , Is there a way of removing duplicates from the field?

Any help would be appreicated

 

[MajP]

Some modification of this idea would work

Like Function

This problem is not soluble. (Stay with me, that was rhetorical.) If your text box contains 292 72 0276 then do you want to match all records with 292? With 72? With 0276? You already said there is no pattern. The computer cannot be a mind reader. (Which is good because by now mine would...

www.access-programmers.co.uk

 

[The_Doc_Man]

If it is a small number of records, do it by hand. Doing this via programming will depend on the reliability of the faulty cases, and you have already show three cases that would become hard to program.

Your "1 Sample Road" case has two poorly formatted entries and one of them only differs from the "good" one by case, not spaces. In both cases, the LAST entry is apparently the good one, but it is not always 3rd. In one case it is 2nd. From a programming viewpoint, you need to be able to establish a rule before you can do anything with programming.

Also, the apparent delimiter is a comma. Will there ever be a case where a comma is a legitimate element of the address?

 

[MajP]

Or a version of this

Removing duplicate text from random records

I've got a table where some of the cells in one column have nearly identical information entered twice. Here are some examples: I need a query to go through the whole table, catalog what data is in the cell, and delete duplicate text. So in my examples, the extra 11/16 49 97, 11/16 61 97...

www.access-programmers.co.uk

As stated in the threads the trick is to make a user interface that make finding possible duplicates very easy. Coming up with a single function to find all cases and fix it is not doable.