472,143 Members | 1,393 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,143 software developers and data experts.

Getting rid of Duplicates in a Field

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.
Sep 28 '20 #1
14 4643
3,653 Expert Mod 2GB

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!

Sep 29 '20 #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.
Sep 29 '20 #3
3,653 Expert Mod 2GB
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!
Sep 29 '20 #4
8,830 Expert 8TB
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.
Sep 29 '20 #5
409 Expert Mod 256MB
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
Sep 29 '20 #6
3,653 Expert Mod 2GB
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....
Sep 29 '20 #7
409 Expert Mod 256MB
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.
Sep 29 '20 #8
3,653 Expert Mod 2GB

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.
Sep 30 '20 #9
409 Expert Mod 256MB
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.
Sep 30 '20 #10
32,498 Expert Mod 16PB
"Not sure why you keep saying 'hepp' when you mean help."
Not sure how old you are C but if you remember back a few years (OK. Decades. I mean decades alright :-( ) when The Beverly Hillbillies was showing on terrestrial TV over here then imagine TwinnyFo (24 in same accent) speaking in a similar dialect.

He tells me it isn't from that show but another I (probably we) are less familiar with but it's about stock car racing in that same region I believe.

Anyway, all I can really say is that Twinny, as you'll see from his post count, is one of our most prolific and helpful expert mods.

All the best - and I hope life out West is treating you well. You can imagine how much fun it is to be close to Town right now but we're managing.
Dec 22 '20 #11
409 Expert Mod 256MB
Hi NeoPa
I'd forgotten all about my slightly irritated responses in this thread. Anyway, thanks for the explanation as well as the derivation of twinnyfo's name.

I believe I'm quite a few years older than you but I have to confess that I never saw the Beverley Hillbillies back in the day.

I do count myself lucky to be living in the middle of nowhere (Exmoor). Even without COVID, I'd find it very hard to live in a town again after so many years away. I'd noticed you hadn't been around for a while. Good luck and stay safe
Dec 22 '20 #12
32,498 Expert Mod 16PB
I believe I'm quite a few years older than you but I have to confess that I never saw the Beverley Hillbillies back in the day.
I wouldn't have known, there again I maybe haven't actually seen you much. When we spoke it was just on the microphone mainly.

I just checked when it stopped showing. It was March 1971. As a point of reference that was about a month after we completed conversion of the currency to decimal!
Dec 23 '20 #13
3,653 Expert Mod 2GB

I believe Post #5 has an adequate outline/solution. OP has not responded to let us know if this approach is acceptable in his situation.
Dec 23 '20 #14
32,498 Expert Mod 16PB
Both posts #4 & #5 describe solutions that appear adequate to me. Are they for you?

If so then you have your answer. If not, please feel free to post a question explaining what you need to know. Your question would have to be in its own separate thread of course.
Dec 23 '20 #15

Post your reply

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

Similar topics

reply views Thread by Child | last post: by
1 post views Thread by John | last post: by
reply views Thread by Sagaert Johan | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.