Auto re-name duplicate fields in query

[IanHallett]

I have a query that creates a SearchName field by extracting the first 6 characters of the SurName field and the first character of the FirstName field.

I get duplicate SearchName field when I have for example a Peter Jones and a Paul Jones - i.e. they both return JonesP.

Is there any way to check the field value for the previous record with the current record and add number 1, for example, to create a different value ?

Any help would be much appreciated.

 

[Remou]

You are storing this search field, aren't you? So you can use DlookUp to check for existing values, say:

'Not tested

'Watch out for De Vere and O'Niell but what to do about Ng and Grey?

sf = Left(Replace(Replace(Me.Surname," ",""),"'",""),6) _
& Left(Me.FirstName,1) & "01"

Do While Not IsNull(DlookUp("[SearchName]","tblNameOfTable","[SearchName]='" & sf & "'"))
   sf=Left(sf,Len(sf)-2) & Format(Val(Right(sf,2))+1,"00")
Loop

Hope I have that right.

That being said, have a search for SoundEx, I think you will find it useful.

 

[IanHallett]

Thanks for your response. I think I may be a little out of my depth here - Should your code be part of the query as an expression or a separate module. I appreciate that I may also be completely missing the point.

Thanks.

 

[Remou]

My fault, I missed the word 'query' in your post. The code was intended for a form where a search field is built into the table. Perhaps if you say what you want to do, someone may have a better idea? To add a number to the end of the field in a query may not be a good idea, because the number may change from run to run.

 

[traingamer]

Am I missing the point here? If you are setting up a search field with 6 characters of last name and first initial, why would you want to differentiate between duplicates? If you do that you'll simply have a complete list of names...

Wouldn't you want to display all JonesP and at some other point let a user select Peter vs. Paul?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Remou]

Gosh, I missed that point altogether, traingamer <sheepish smile>