Creating Reference numbers

[Robster]

I need to check if the reference number my query creates, already exists in the table it's being appended to. EG does the query on table A which creates the value '1403ABCDEF01' already exist in table B in the reference field. if it does, change the last 2 digits to 02 and check again until value isnt found and so can be imported.

Please let me know if I need to clarify further.

 

[jdraw]

It might be helpful to readers if you could describe your database and the logic surrounding your post.
Arbitrarily changing a reference sounds a little "iffy".
If this is a new record, why not just add the new record and number it based on your numbering scheme.

It sounds like we're missing a piece of the puzzle.

 

[Robster]

Sorry, am new to this.

The database is for inputting sales leads, so there is a form which saves the data to a table. A query then runs to Append that data to the main table and creates a reference number made up of the client name initials and the date (YYMM).
EG this month 03/2014 and Smiths & Partners - SP would become 1403SP

what I need is to add a 'version number' to it so if it's the first one then make it 1403SP01 but if 1403SP01 exists then make it 1403SP02 etc.

 

[jdraw]

I don't know how far along you are, but I'd discourage your codification scheme.
What's wrong with any unique number? You can record the LeadDate and LeadName in separate fields.You can search, order by any of these fields or combinations.
What is the purpose of your codification?
In database it is advisable to have 1 fact 1 field.

If the leadName can have multiple associated leadDates, you could consider a tblLeads and another tblLeadContactDates depending on your needs.