If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Locating duplicate records in memo fields

In my data base a series of records are duplicated in memo fields and this is ongoing situation. I wish to knock out these duplicates. The duplicates are in a field of a table that is on the many side of a one to many and the other fields only have time, date, ID Number so I would knock out the whole record.

Of course if it was not a memo field then this would be very simple.

So far the best I can come up with is to have a new field in the query based on the Len function. The counting works OK. I put about 12 pages of writing in a field and it counted and if I added or removed a charater or added a space etc the change was reflected.

Does anybody have a better way of doing it or does anyone see any special problems with using Len to try and pick up the duplicates

Google around for "SELECT INTO queries" & "SELECT DISTINCT" and read up a bit. It's not too bad once you have your head around the general concept.

I have 3 very big Access 95 books that have a lot of that stuff but I have never gone into before because I have never had to.

I notice the Access wizard Find Duplicates does not want any part of a memo field. I also tried a macro opening a form for matching records on the memo field and that worker up to the 250 character limit.

I change the the OneOffMerge to a date field that has heaps of duplicates and it worked, that is only returned one of each different date.

My Access 95 Help seems to indicate that DinstinctRow will pull the duplicates but only if two tables are involved. However that did not work.

One I found where I was going I then changed it to your suggestion.

So it looks like I am back to using the Len function. I am very sure it would do the job as the chance of two sets of notes being different but having the same character count would be very remote. However, it is still not the perfect solution.

since you are A95, here is a DAO proposal. it's great to do a bit of code before breakfast on a sunday morning - works up the appetite.
it is going to be very slow, but you are only going to do it once, so who cares.

you need
a form with a button named "GO"
your original table "SalesNotes" containing a memo field "OneOffMerge"
a copy of the "SalesNotes" called "nodupeNotes"

Thanks for that. I will have a go tomorrow. I am heading to bed soon for an early Sunday night.

One thing I did find interesting with A95 and the memo field was that although a query won't have a thing do with a memo field (including the Find Duplicate wizard) I could open a matching record with a macro BUT only up to 250 characters approx. I bet if I checked further it would be 255. One I had more characters then the message came up about too much text or whatever it said.

Using the Len function against records with several pages of writing is a bit different to a normal calculated field in the sense that when alter the memo field by a character or two and then click elsewhere there is no change on the Len field except when you sort A to Z and of course if close and reopen the query.