By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
463,081 Members | 545 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 463,081 IT Pros & Developers. It's quick & easy.

Getting rid of Duplicates in a Field

P: 2
Hi I'm new to VBA and Access and was wondering if anyone knew of a way to go through my field to replace the duplicated string separated by a new line and carriage return:

Field Ex:


I really appreciate any help.
3 Weeks Ago #1
Share this Question
Share on Google+
9 Replies

Expert Mod 2.5K+
P: 3,542

Welcome to Bytes!

I'm not sure I understand your question. Is your first list of characters the contents of one field in one record or is this a series of records from one field in a table?

The answer to this will certainly determine how we approach this solution.

Standing by to hepp!

3 Weeks Ago #2

P: 2

They are a list of one field in one record. So the record's field would read like the long above list. I would like help figuring out how I can "scan" through them and take out any duplicates.

Note: they seem to all be in alphabetical order, so in no cases will it go like A /n/r A /n/r B /n/r A for example.
3 Weeks Ago #3

Expert Mod 2.5K+
P: 3,542
So, I will "describe" the solution, without coding it out, but we can always work toward a final solution if needed.

I would create a user-defined function that takes the value of the field ans assigns that value to an array, using the Split() function, with the separating value of Chr(13)--which is the carriage return. Once it is in an array, assign the first character to a string variable. Cycle through the values in the array and when a value other than the current value is found, add a carriage return and that character to your string variable. After going through the entire array, return the value of that string.

Again, this is a description, but at the moment I am unable to write out code for this, but I'm glad to continue efforts on this.

If you have no idea what I am talking about, then we will have to go through things step by step. That's OK - everyone has to begin learning VBA at some point.

Hope this hepps!
3 Weeks Ago #4

Expert 5K+
P: 8,746
Although not an easy task, I'll try to step into twinnyfo's shoes for a minute, and hopefully I'll be on the right track.
  1. I have no access to Access (no pun intended) right now, so I will simulate a Field Value in Excel, then pass it to a Function to be analyzed.
    Expand|Select|Wrap|Line Numbers
    1. Dim strTest As String
    3. 'Simulate Field Value
    4. strTest = "A" & vbCrLf & "A" & vbCrLf & "A" & vbCrLf & "B" & vbCrLf & _
    5.           "B" & vbCrLf & "B" & vbCrLf & "C" & vbCrLf & "C" & vbCrLf & _
    6.           "C" & vbCrLf & "C"
    8. 'Debug.Print strTest         'Make sure I got it right!
    10. Debug.Print fAnalyzeString(strTest)
  2. As twinnyfo has suggested, I'll split the String into an Array consisting of individual Elements with a CrLf being the Delimiter.
  3. I will use a variable and extract the 1st character from the Array.
  4. Now, check this character against every element in the Array, and if there is no match, start building the Variable String. You are actually checking the rightmost character of the Build String against elements of the Array.
  5. Repeat this process until every character in the Build String has been checked against every element in the Array.
  6. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fAnalyzeString(strString As String) As String
    2. Dim varString As Variant
    3. Dim strChars As String
    4. Dim intCtr1 As Integer
    5. Dim intCtr2 As Integer
    7. varString = Split(strString, vbCrLf)
    9. strChars = varString(0)
    11. For intCtr1 = 1 To Len(strChars)
    12.   For intCtr2 = LBound(varString) To UBound(varString)
    13.     If Right$(strChars, 1) <> varString(intCtr2) Then
    14.       strChars = strChars & vbCrLf & varString(intCtr2)
    15.     End If
    16.   Next
    17. Next
    19. fAnalyzeString = strChars
    20. End Function
  7. OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. A
    2. B
    3. C
P.S. - Looking at my explanation, I do realize that it may not be the best in the world. Perhaps twinnyfo can make it much clearer.
3 Weeks Ago #5

P: 40
There are many ways of dealing with this situation. For example:

Create a new empty table with the same fields but index your field (the one with dupes) and set as no dupes allowed.

Now create an append query to copy all data into the new table.
Because you have set a unique index, only one record will be appended for each duplicate

Hope that helps
3 Weeks Ago #6

Expert Mod 2.5K+
P: 3,542
I have not checked your math, but your approach is spot on to the direction I was going. It's a great outline to start with.

If you read the rest of this thread carefully, you will notice that your solution does not address the issue. Note, that this was my first question to OP, and we determined that the field itself has these duplicate values, not a series of fields.

I hope this has given you something to work with, or at least begin work on a solution for your table.

Standing by with more hepp if you need it....
3 Weeks Ago #7

P: 40
I've re-read the thread carefully but your response wasn't clear!
I think you meant a single record in a field has these duplicate values not a series of records. If that is the case, my suggestion won't work.
3 Weeks Ago #8

Expert Mod 2.5K+
P: 3,542

Fields don’t have records, records have fields. Your solution is based upon one field among multiple records. The question is about one field in one record that has a “stacked” value with duplicate values in that “stack”.

As a side note, although your method may have worked under a different scenario, it is usually not wise to force the loss of a record via index violations. This would require adequate and accurate error handling to keep the DB from halting, not to mention the loss of all the data in the other fields of each record rejected.

Hope this makes sense and hope it hepps you understand this issue a bit better.
3 Weeks Ago #9

P: 40
Yes, my answer was also poorly worded regarding fields and records.
Thank you but I already understood the issue without your additional explanation
I'm also fully aware of potential side effects of deleting any data.
Not sure why you keep saying 'hepp' when you mean help.
3 Weeks Ago #10

Post your reply

Sign in to post your reply or Sign up for a free account.