By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 424,949 Members | 904 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

# Access Customer ID...

 100+ P: 118 Hey all, my first post.. I am making an Access database in which the Customer ID has to be the first 3 letters of their last name followed by a random 4 digit number. I have a 'Last Name' field; how would I take the first three letters from the Last Name and add the random numbers? (With a formula, rather than doing it manually..) Also, how would I then validate it so that it can only be 3 letters, followed by 4 numbers? Sorry if this is a n00bish question. Sam Mar 13 '07 #1
Share this Question
5 Replies

 Expert Mod 10K+ P: 12,347 This will be different depending on where you want to use it. Expand|Select|Wrap|Line Numbers [Customer ID] = Left([Last Name], 3) & Int(9000*Rnd + 1000) Mar 13 '07 #2

 Expert Mod 15k+ P: 31,342 This will be different depending on where you want to use it. Expand|Select|Wrap|Line Numbers [Customer ID] = Left([Last Name], 3) & Int(9000*Rnd + 1000) You say you need a random number, but really it needs to be unique rather. After creating a possible string you would need to check that it doesn't already exist. Otherwise : Expand|Select|Wrap|Line Numbers [CustomerID]=Left([LastName],3) & Format(Val(Nz(DMax("Val(Right([CustomerID],4))","YourTable","[CustomerID] Like '" & Left([LastName],3) & "'"),"0")),"0000") Mar 15 '07 #3

 100+ P: 118 Cool, thanks. Is it possible to do it in table level, at all? Sam Mar 19 '07 #4

 Expert Mod 10K+ P: 12,347 No, you can do it at query level though. Which will require code because you have to check for dupes. Mar 19 '07 #5

 Expert Mod 15k+ P: 31,342 Cool, thanks. Is it possible to do it in table level, at all? Sam What do you mean. The second bit of code in my post should do what you need, but will not work as is due to lack of relevant information. What does 'to do it in table level' mean? Mar 19 '07 #6

### Post your reply

Sign in to post your reply or Sign up for a free account.