How to automatically manipulate duplicate data?

[Briscoe]

In order for my output file to be loaded into another system, the "account numbers" can not be duplicated. Although one "account number" may be the same as another "account number" listed, the file does not contain duplicate information. I would like to automatically add a space (preferably), or a character (if I must) to one or more of the duplicate numbers until it is no longer a duplicate. How can I do that?

Thank you!

 

[rubbernilly]

If you are doing a simple OutputTo method to get a text file, then I'd suggest adding a text field called "UniqueAccountNumber" to the table. Export this field rather than the AccountNumber field by masking the field name in a query and exporting the query rather than the table.

To get a unique account number into the UniqueAccountNumber field, try this:

dim sNewAccount as string
dim a as long
dim rs as DAO.Recordset

set rs = CurrentDB.OpenRecordset("YourTable")

while not rs.eof
  a = 1
  while Len(Nz(dlookup("UniqueAccountNumber","YourTable","UniqueAccountNumber = '" & rs!AccountNumber & "_" & a & "'"),""))>0
    a = a+1
  Wend
  rs.edit
    rs!UniqueAccountNumber = rs!AccountNumber & "_" & a
  rs.Update
  rs.movenext
wend
set rs = nothing

HTH

 

[Briscoe]

I will try that...thank you!