Query that generates 2 Letters and four numbers thereafter

[fuuumanchu]

In addition to my previous question, I need to create a query that will work in forms, that will automatically generate a customer ID number.

1. If the customer is new it will generate an Id that will take the first initial of the customer's FirstName and the first initial of the customer's LastName. Then a four digit number will follow after.

2. The customer ID must be unique and the four digit number must automatically generate starting from 1000 not before it.

 

[gcole]

use the Mid() command to pick up the first initial of the first and last name. Assign it to your name varialbe. Track you customer numbers in a table so you know the last number assigned. Set up an If staement looking at the Len(str(number)). If it is one add your name varaiable +"000"+ the str(number).

 

[MichaelRed]

see faq700-184 It will need some minor modification, but will at least show you an approach.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over