Change same values

[MkIIISupra]

I have a table with 2571 records of which many have the same name. This table is based off of an Oracle DB that I can only read and not modify. Anyhow my constraints are as follows:
Len = 13
Type = Text
Cannot be Same

I am trying to loop through and make a simple change to each duplicate, like drop off 1 or 2 of the last chars based on Len. But I can't seem to get it to work.

There are some instances where I have 2 dupes and those work fine, but if there is 3 or more then I can't get the changes to take effect. Either I am really tired or I dunno what! Here is a sample of come of the code I have been trying to use.

I probably could use a StrComp, but I have had little success with it as well...

Dim cnt As Integer
Dim a As String ' a is used to hold initial value.
Dim b As String ' b is used to drop 2 off
Dim c As Integer ' is a len val

a = Me.GISNAME.Value
DoCmd.GoToRecord , , acNext

Do
    If (Me.GISNAME.Value) = a Then
        c = Len(Me.GISNAME) - 2
        Me.GISNAME.Value = Left$(Me.GISNAME, c)
        a = Me.GISNAME.Value
        
    ElseIf (Me.GISNAME.Value) <> a Then
        a = Me.GISNAME.Value
    End If
    
    cnt = cnt + 1
    DoCmd.GoToRecord , , acNext
    
Loop Until cnt = 2571

Thanks for any help you can give!

One by one the penguins steal my sanity!

 

[mas5]

I see a flaw in the logic when you reset a to the truncated me.gisname.value. You don't want to reset the a value. I'm assuming that you want each repeated value to be truncated more than the previous. So if you have, let's say the string HELEN three times, you want to get HELEN, HEL, H. Try the following:

a = Me.GISNAME.Value
b = 0
DoCmd.GoToRecord , , acNext

Do
If (Me.GISNAME.Value) = a Then
b = b + 2 ' or plus 1
c = Len(Me.GISNAME) - b
' make sure that c doesn't end up being negative
Me.GISNAME.Value = Left$(Me.GISNAME, c)
' don't reset a to the truncated value

ElseIf (Me.GISNAME.Value) <> a Then
b = 0
a = Me.GISNAME.Value
End If

cnt = cnt + 1
DoCmd.GoToRecord , , acNext

Loop Until cnt = 2571


Hope this helps.

 

[MkIIISupra]

Thanks, I'll give it a try in a bit!

One by one the penguins steal my sanity!