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
Bytes IT Community
+ 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
Share on Google+
5 Replies


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

NeoPa
Expert Mod 15k+
P: 31,342
This will be different depending on where you want to use it.
Expand|Select|Wrap|Line Numbers
  1. [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
  1. [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

Rabbit
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

NeoPa
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.