customer ID help - automatically create a structured ID HELP!!! (1 Viewer)

Chrisopia

Registered User.
Local time
Today, 08:07
Joined
Jul 18, 2008
Messages
279
Hi there,

Probably a really simply task that I cant figure out - blonde moment perhaps?

I have a customer ID as the primary key, it is to be constructed from 2 letters and 2 numbers.

For example, Joe Blogs would appear as BL01


Is it possible to create an ID from reading the first 2 letters of the last name and adding 2 digits to the end?

How would i go about this?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:07
Joined
Feb 19, 2002
Messages
42,970
I'm sure this seems like a good idea now but you are not giving yourself enough room for expansion. Think BIG. I would use at least 4 numeric digits if I were going to do this. I would also use an autonumber as the primary key and then create a unique index on the combination of the text field and the numeric field. You can use DMax() to find the next available number:

Me.SeqNum = Nz(DMax("SeqNum","YourTable", "LetterCode = '" & Me.LetterCode & "'"), 0) +1

This expression handles the assignment even when you are using a letter combination for the first time.

Then when you want to display the CustomerNumber use:

LetterCode & Format(SeqNum,"0000")
 

wazz

Super Moderator
Local time
Today, 23:07
Joined
Jun 29, 2004
Messages
1,711
what happens after 99?
...
too slow again. :p
 

Chrisopia

Registered User.
Local time
Today, 08:07
Joined
Jul 18, 2008
Messages
279
Looks useful... but how do I used this?


Me.SeqNum = Nz(DMax("SeqNum","YourTable", "LetterCode = '" & Me.LetterCode & "'"), 0) +1

Table is called "TBLCustomer" and the field is called "Last Name"
The field this is entered in is called "CustomerID"

I choose 2 numbers because in the past we havent even got past 04 in our filling system because last names are so diverse.

Also wehre do I enter this?
 

neileg

AWF VIP
Local time
Today, 15:07
Joined
Dec 4, 2002
Messages
5,975
By all means create your ID, just don't use it as a PK. I have seen too many occasions where there is a need to change IDs because of some business process or other software. If you use a simple autonumber as the PK you can still hold the Customer ID and be able to change it in the future without a huge update of your DB.
 

Chrisopia

Registered User.
Local time
Today, 08:07
Joined
Jul 18, 2008
Messages
279
By all means create your ID, just don't use it as a PK. I have seen too many occasions where there is a need to change IDs because of some business process or other software. If you use a simple autonumber as the PK you can still hold the Customer ID and be able to change it in the future without a huge update of your DB.

That's all very well, but it still didn't answer my question?
 

neileg

AWF VIP
Local time
Today, 15:07
Joined
Dec 4, 2002
Messages
5,975
As a calculated field in a query. If you feel you need to store this value (you don't, you should calculate it when you need it) then use this in an update query or as the source for a control on a form.
 

Chrisopia

Registered User.
Local time
Today, 08:07
Joined
Jul 18, 2008
Messages
279
Break it down?

Me.SeqNum = Nz(DMax("SeqNum","YourTable", "LetterCode = '" & Me.LetterCode & "'"), 0) +1

What is
Me
SeqNum
Nz
YourTable
LetterCode
??

This will help me understand it more to use it.

Is it reading the field "LastName" and taking the first 2 letters from it to add to the sequence?
 

neileg

AWF VIP
Local time
Today, 15:07
Joined
Dec 4, 2002
Messages
5,975
Me.SeqNum = Nz(DMax("SeqNum","YourTable", "LetterCode = '" & Me.LetterCode & "'"), 0) +1

What is
Me
This is Access shorthand for the current object.
You have to insert the name of the field you have used
Returns a value you specify if the result is otherwise null. Defaults to zero hence Null to zero
YourTable
Insert the name of your table
LetterCode
Insert the name of your field
 

Chrisopia

Registered User.
Local time
Today, 08:07
Joined
Jul 18, 2008
Messages
279
Hey thanks, so far so good...

but now just to get it to find the first 2 letters from the field "lastName" and add prefix it to the number?

unless Im doing it wrong?
 

neileg

AWF VIP
Local time
Today, 15:07
Joined
Dec 4, 2002
Messages
5,975
Use the Left() function to get the first two letters and the concatenate this with the '&' operator.
 

Chrisopia

Registered User.
Local time
Today, 08:07
Joined
Jul 18, 2008
Messages
279
Great Stuff

Code:
=Left([Last name],2) & Nz(DMax("Number","TBLCustomer"),0)

I'm onto something here...

just now I need to add extra naughts before the number.

For instance, right now, this code shows

"GR1"
When really it's meant to show
"GR01"
 

Chrisopia

Registered User.
Local time
Today, 08:07
Joined
Jul 18, 2008
Messages
279
Ah - I've encountered another problem,

Lets say 5 people:

B Gregory
C Gregory
D Gregory
J Blogs
K Blogs

Right now these would show up as:

GR1
GR2
GR3
BL4
BL5

When really I need it to discover that BL is different to GR, for example;

GR1
GR2
GR3
BL1
BL2

:confused::confused::confused::confused::confused:
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:07
Joined
Feb 19, 2002
Messages
42,970
Chrisopia,
Go back to my original post.
1. I showed you the format of the DMax() so that it will renumber WITHIN the letter groups.
2. I showed you how to concatenate the letters and numbers and prefix the numbers with leading zeros.
3. I reminded you to NOT use this field as your primary key but to create a unique index instead.
 

Users who are viewing this thread

Top Bottom