Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to automatically manipulate duplicate data?

Status
Not open for further replies.

Briscoe

Technical User
Joined
Feb 10, 2003
Messages
29
Location
US
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!
 
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:

Code:
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
 
I will try that...thank you!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top