AutoNumber

J

JLong

Is it possible to create a field, using the AutoNumber
data type, that would create an ID number with the first
two characters from the FirstName and LastName fields plus
some autonumber? I have been trying something like this
in the field's format.

right([FirstName],2) & right([LastName],2-##

Can someone help me? Thanks in advance.
 
D

Douglas J. Steele

It's not possible, and, to be perfectly honest, it wouldn't be a good idea
even if it were. Relational Database Theory states that you should only
store one value per field, so you really should keep FirstName, LastName and
ID number in 3 separate fields. You can generate such an ID for display
purposes should you really need to, but you shouldn't store it.
 
G

Guest

Doug, I agree with you. I am not trying to combine these
three fields. I just wanted to know, if it was possible
to achieve that and how. I have an entry form where new
records are added, so I was trying to see if it was
possible to create the ID field by using the first two
characters of the FirstName or LastName fields or both
plus some autonumber. For example for Adam Smith the ID
would be AD-01 or ADSM-01, where the numeric part would be
an autonumber. Do you know how to do this? I would really
appreciate your help.
 
L

Lynn Trapp

Give this a try in a query.

Left([Firstname],2) & Left([Lastname],2) & "-" & [YourAutoNumberField]
 
G

Gijs Beukenoot

Lynn Trapp was zeer hard aan het denken :
Give this a try in a query.

Left([Firstname],2) & Left([Lastname],2) & "-" & [YourAutoNumberField]

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Doug, I agree with you. I am not trying to combine these
three fields. I just wanted to know, if it was possible
to achieve that and how. I have an entry form where new
records are added, so I was trying to see if it was
possible to create the ID field by using the first two
characters of the FirstName or LastName fields or both
plus some autonumber. For example for Adam Smith the ID
would be AD-01 or ADSM-01, where the numeric part would be
an autonumber. Do you know how to do this? I would really
appreciate your help.
Click to expand...
Click to expand...

And what exactly would be the base of this autonumber? Each combination
it's own number like AS01, AS02, LT01, AS03. I can imagine some
combinations would go over the 99 count...
But anyway, you should build a query retrieving the highest used number
from the letter-combination
SELECT TOP 1 Right(<IDfield>,2) FROM <table> WHERE LEFT( <idField>,2) =
Left([Firstname],1) & Left([Lastname],1) ORDER BY <IDField> DESC
Now you should have the highest number in your recordset. Add one and
build the new key. If the recordset is empty, there wasn't any previous
entry with those letters.
You could probably also use a count statement since that gives you the
total used.

Problems? Yes, to name a few :
- More then 99 entries for a lettercombination
- Deleted entries (AS02 is deleted; this query won't solve that
problem, you'll have to use code for retrieving that one)
- Two (or more) users running this procedure at the same time could end
up with the same ID, so you must check to see if your ID is free and,
preferably, reserve it by adding it to the database as soon as possible
 
G

Guest

I see your solutions are based on queries, but what about
format property (custom format) of the ID field? Is there
a way of setting a custom format or expression to create
the ID, may be a custom mask or something? I an new to
access, so I am not sure if this can or can't be done.
About the min and max number of records, no problem
because I will be creating a new table. I tried something
like what Lynn suggested on the format property of the Id
field, but it didn't work.
-----Original Message-----
Lynn Trapp was zeer hard aan het denken :
Give this a try in a query.

Left([Firstname],2) & Left([Lastname],2) & "-" & [YourAutoNumberField]

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Doug, I agree with you. I am not trying to combine these
three fields. I just wanted to know, if it was possible
to achieve that and how. I have an entry form where new
records are added, so I was trying to see if it was
possible to create the ID field by using the first two
characters of the FirstName or LastName fields or both
plus some autonumber. For example for Adam Smith the ID
would be AD-01 or ADSM-01, where the numeric part would be
an autonumber. Do you know how to do this? I would really
appreciate your help.
Click to expand...
Click to expand...

And what exactly would be the base of this autonumber? Each combination
it's own number like AS01, AS02, LT01, AS03. I can imagine some
combinations would go over the 99 count...
But anyway, you should build a query retrieving the highest used number
from the letter-combination
SELECT TOP 1 Right(<IDfield>,2) FROM <table> WHERE LEFT(
Click to expand...
Left([Firstname],1) & Left([Lastname],1) ORDER BY
Click to expand...
 
D

Douglas J. Steele

There's no way to do this using a format property, since you're trying to
get values from different fields. You must use code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I see your solutions are based on queries, but what about
format property (custom format) of the ID field? Is there
a way of setting a custom format or expression to create
the ID, may be a custom mask or something? I an new to
access, so I am not sure if this can or can't be done.
About the min and max number of records, no problem
because I will be creating a new table. I tried something
like what Lynn suggested on the format property of the Id
field, but it didn't work.
-----Original Message-----
Lynn Trapp was zeer hard aan het denken :
Give this a try in a query.

Left([Firstname],2) & Left([Lastname],2) & "-" & [YourAutoNumberField]

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Doug, I agree with you. I am not trying to combine these
three fields. I just wanted to know, if it was possible
to achieve that and how. I have an entry form where new
records are added, so I was trying to see if it was
possible to create the ID field by using the first two
characters of the FirstName or LastName fields or both
plus some autonumber. For example for Adam Smith the ID
would be AD-01 or ADSM-01, where the numeric part would be
an autonumber. Do you know how to do this? I would really
appreciate your help.
Click to expand...

And what exactly would be the base of this autonumber? Each combination
it's own number like AS01, AS02, LT01, AS03. I can imagine some
combinations would go over the 99 count...
But anyway, you should build a query retrieving the highest used number
from the letter-combination
SELECT TOP 1 Right(<IDfield>,2) FROM <table> WHERE LEFT(
Click to expand...
Left([Firstname],1) & Left([Lastname],1) ORDER BY
Click to expand...
Now you should have the highest number in your recordset. Add one and
build the new key. If the recordset is empty, there wasn't any previous
entry with those letters.
You could probably also use a count statement since that gives you the
total used.

Problems? Yes, to name a few :
- More then 99 entries for a lettercombination
- Deleted entries (AS02 is deleted; this query won't solve that
problem, you'll have to use code for retrieving that one)
- Two (or more) users running this procedure at the same time could end
up with the same ID, so you must check to see if your ID is free and,
preferably, reserve it by adding it to the database as soon as possible

--
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. <snap>

.
Click to expand...
Click to expand...
 
Top