Auto Numbering

Hi

Can I make the Auto Numbering Field Add some letters depending on other two
fields?

Like:

Auto Number Name Source
SB001C Shiblie Bshara Call

As SB fro the Name Column & C from the Source.


Thanks in Advance,

Best Regards,
Shiblie.B

A couple of things:

1. Autonumbers are good for generating unique values for primary keys, but
otherwise really should remain hidden (unless you don't care that there may
be gaps or perhaps negative numbers, etc).

2. Store the autonumber in its own field and use another field, or a query,
to display it the way you want to see it.
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________


"Shiblie Bshara" wrote:

Hi

Can I make the Auto Numbering Field Add some letters depending on other two
fields?

Like:

Auto Number Name Source
SB001C Shiblie Bshara Call

As SB fro the Name Column & C from the Source.


Thanks in Advance,

Best Regards,
Shiblie.B

Thanks for your reply, but that did not solve my problem yet!

I'll use another column but how to link it as to use the Initials as a start
and a source as an ending of the Number like:

case source: E-mail, Handling Agent: John Smith, Case Number:
005

I want it to appear like this: (JS005E)
__________________________________________________ _________
"boblarson" wrote in message
...
A couple of things:

1. Autonumbers are good for generating unique values for primary keys, but
otherwise really should remain hidden (unless you don't care that there
may
be gaps or perhaps negative numbers, etc).

2. Store the autonumber in its own field and use another field, or a
query,
to display it the way you want to see it.
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________


"Shiblie Bshara" wrote:

Hi

Can I make the Auto Numbering Field Add some letters depending on other
two
fields?

Like:

Auto Number Name Source
SB001C Shiblie Bshara Call

As SB fro the Name Column & C from the Source.


Thanks in Advance,

Best Regards,
Shiblie.B

It is going to be difficult to extract the initials of the handling
agent. That would probably be best handled in a custom VBA function.

You can try

Field: MyNumber: Left([Handling Agent],1) & Mid([Handling
Agent],Instr(1,[Handling Agent]," ")+1,1) & [Case Number] & Left([Case
Source],1)

Several problems with this. If Handling agent has two spaces between
first and last name you will get a space for the second character.

If Case number is an autonumber field you won't get leading zeroes
without applying a format to the number when you add it into the string.

So you might need something like:
Format([Case Number],"000000")



'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


Shiblie Bshara wrote:
Thanks for your reply, but that did not solve my problem yet!

I'll use another column but how to link it as to use the Initials as a
start and a source as an ending of the Number like:

case source: E-mail, Handling Agent: John Smith, Case
Number: 005

I want it to appear like this: (JS005E)
__________________________________________________ _________
"boblarson" wrote in message
...
A couple of things:

1. Autonumbers are good for generating unique values for primary keys,
but
otherwise really should remain hidden (unless you don't care that
there may
be gaps or perhaps negative numbers, etc).

2. Store the autonumber in its own field and use another field, or a
query,
to display it the way you want to see it.
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________


"Shiblie Bshara" wrote:

Hi

Can I make the Auto Numbering Field Add some letters depending on
other two
fields?

Like:

Auto Number Name Source
SB001C Shiblie Bshara Call

As SB fro the Name Column & C from the Source.


Thanks in Advance,

Best Regards,
Shiblie.B

A few further points to consider:

1. An autonumber is designed only to guarantee unique values not sequential
ones. If an uninterrupted sequence is important then don't use an autonumber
bit a straightforward long integer number data type and generate the next
number in sequence when a record is inserted, e.g. in the BeforeInsert event
procedure of a form:

Me.[Case Number] = Nz(Dmax("[Case Number]", "Cases"),0)+1

where Cases is the table name.

This is fine in a single user environment, but could give rise to conflicts
in a multi-user environment where two or more users are inserting records
simultaneously. There are various ways of handling this, one of which, and
which also allows the next number in the sequence to be reset, you'll find at
the following link:


http://community.netscape.com/n/pfx/...g=ws-msdevapps


2. As the Handling Agent's name is being stored in a column in the table
you don't need to include the initials in an 'extended case number' column.
Nor should you do so as it introduces redundancy and the possibility of
inconsistent data being entered. The 'extended case number' should be
computed on the fly at runtime. You could use the following expression as
the ControlSource property of an unbound control in a form or report for
instance:

Left([Handling Agent],1) & Mid([Handling Agent],InstrRev([Handling Agent],"
")+1,1) & Format([CaseNumber],"000000") & Left([Case Source],1)

You can of course use a similar expression for a computed column in a query.

Using the InstrRev function does cater for the possibility of the name
having a middle name or initial, or there being two spaces before the last
name, but would for a name such as 'Victoria de los Angeles' the last name
initial would be returned as 'A' rather than 'd'. Its always best to have
separate FirstName and LastName columns in a table.

3. A further point about names is that they can be duplicated, so you
should really have a separate table for the handling agents, with one row per
person and a primary key, which can be an autonumber, such as
HandlingAgentID. You'd then have a foreign key HandlingAgentID column (not
an autonumber this time) in your cases table. You'd base your form or report
on a query joining the Cases and HandlingAgents tables of course to pull in
the names.

4. You should also have a separate Sources table with one row per source
type. As the text descriptions will be unique values this can be the primary
key. The Source column in the cases table will be a foreign key referencing
this. By enforcing referential integrity and cascade updates in the
relationship between Cases and Sources you ensure that only valid Source
values can be entered in Cases.

Ken Sheridan
Stafford, England

"Shiblie Bshara" wrote:

Thanks for your reply, but that did not solve my problem yet!

I'll use another column but how to link it as to use the Initials as a start
and a source as an ending of the Number like:

case source: E-mail, Handling Agent: John Smith, Case Number:
005

I want it to appear like this: (JS005E)
__________________________________________________ _________
"boblarson" wrote in message
...
A couple of things:

1. Autonumbers are good for generating unique values for primary keys, but
otherwise really should remain hidden (unless you don't care that there
may
be gaps or perhaps negative numbers, etc).

2. Store the autonumber in its own field and use another field, or a
query,
to display it the way you want to see it.
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________


"Shiblie Bshara" wrote:

Hi

Can I make the Auto Numbering Field Add some letters depending on other
two
fields?

Like:

Auto Number Name Source
SB001C Shiblie Bshara Call

As SB fro the Name Column & C from the Source.


Thanks in Advance,

Best Regards,
Shiblie.B