AutoNumber Generation

[mphil.star]

I am creating a certain program and in that program i want to add a
field which will be creating numbers with text in sequence older "
Like MPL 001/08 to unlimited " And 08 be current year. This will
onlybe possible if that field is enebled, if the condition of enebling
this field still is false then the field returns the previous value
that is it maintances the previous record number.



please assist as soon as possible

 

[a a r o n . k e m p f]

if you were using SQL Server, you could use UDF in order to make this
your real physical primary key.
with Access you're stuck writing the code yourself-- or letting the
user type it in-- or use a surrogate key

 

[Jeff Boyce]

The folks who help here in these newsgroups are largely volunteers. If they
have experience, ideas and time to help, they will.

If you have a tight deadline ("please assist as soon as possible"), you
might need to look into hiring some help.

If you want to have an identifying value like "MPL 001/08", "MPL 002/08",
.... "MPL 001/09", ..., use a query to combine three separate 'facts'. Don't
store all three pieces ("MPL", "001", "08") plus the punctuation in a single
field in a table.

Instead, use one field to hold the code (?"MPL"?) ... and if that is a
constant value, you don't even need to store it!

Use another field to hold a sequence number (an integer, formatted with
something like Format([YourSeqNo],"000"). By the way, if you only have
three places ("001") in the formatting of that sequence number, you won't be
able to tell the difference between sequence number 003 and sequence number
19,003 -- how many records are you expecting?

Then, if you want to show the last two digits of the current year (e.g.,
"08"), one way to do this is to have a field in which you store a date (or
date/time) value, say, when the record was created, then use a query to get
only the last two digits.

Finally, to come up with your "number", you'd use a query with something
like:

MyNumber: "MPL" & " " & [MySequenceNumber] & "/" &
Right(Year([YourDateField]),2)

You'll need to create your own procedure to:

* test to see if the "08" still applies or needs to be "09"
* check for the maximum sequence number for the applicable 'year'
* add one

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

?And who creates the UDF? Isn't that roughly the same as "you're stuck with
writing the code"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
if you were using SQL Server, you could use UDF in order to make this
your real physical primary key.
with Access you're stuck writing the code yourself-- or letting the
user type it in-- or use a surrogate key

 

[Larry Linson]

if you were using SQL Server, you could use UDF in order to
make this your real physical primary key. with Access you're
stuck writing the code yourself-- or letting the user type it in--
or use a surrogate key

Combining "pieces" with different uses and definitions into a single
physical Field is an invitation to hassles and irritations when you need to
use those pieces in the future. Perhaps Mr. Kempf's actual development
experience is so light that he does not realize that.

You can keep the information in separate fields, define those as a
multi-Field key (either in Jet, ACE, SQL Server, or any other server DB with
which I am familiar), and simply concatenate them for display to human
users, if that is what they are accustomed to seeing. And, you should, to
avoid complexities in enhancing your database in the future.

If you feel _compelled_ to complicate your life by creating that key in a
separate single Field, the "code" you'd have to write, in either SQL Server
(as Mr. Kempf recommends) or in Access (Jet or ACE) as you apparently are
doing now, is almost trivially simple. On the other hand, if SQL Server can
"divine" what I need, and miraculously write the UDFs to do what it has
divined that I need, perhaps I have not given it enough credit.

Others have made similar suggestions, but perhaps did not emphasize that you
can easily have a multi-Field key (and some of them, I know, do know that is
the case).

Larry Linson
Microsoft Office Access MVP

 

[a a r o n . k e m p f]

nope; you can use the same UDF wherver you want.
It lives on the DB SERVER

Access doesn't support code reuse.. of course you'll probably
disagree.

But having to copy a query to 20 different desktops--is just a waste
of time.
If you just used ADP and kept your logic- where it belonged- your life
would be a lot easier

 

[a a r o n . k e m p f]

with SQL Server, you could have the seperate peices as seperate
fields; and then concatenate them together AS A COMPUTED/CALCULATED
COLUMN
then you would have the best of both worlds.

but let me guess-- you don't know anything about computed / calculated
columns.. because Jet doesnt' support them.
And you're stupid enough to assume that JUST because jet doesn't have
them-- that means that you don't need them, huh?

-Aaron

 

[a a r o n . k e m p f]

multi-field keys are a pain in the ass and not reccomended for most
situations.
but then again-- it really depends on what you're trying to do.

that is one of the KEY reasons that SQL Server allows you to make a
single column PK, based off of multiple PIECES of data.

 

[mphil.star]

The folks who help here in these newsgroups are largely volunteers.  Ifthey
have experience, ideas and time to help, they will.

If you have a tight deadline ("please assist as soon as possible"), you
might need to look into hiring some help.

If you want to have an identifying value like "MPL 001/08", "MPL 002/08",
... "MPL 001/09", ..., use a query to combine three separate 'facts'.  Don't
store all three pieces ("MPL", "001", "08") plus the punctuation in a single
field in a table.

Instead, use one field to hold the code (?"MPL"?) ... and if that is a
constant value, you don't even need to store it!

Use another field to hold a sequence number (an integer, formatted with
something like Format([YourSeqNo],"000").  By the way, if you only have
three places ("001") in the formatting of that sequence number, you won'tbe
able to tell the difference between sequence number 003 and sequence number
19,003 -- how many records are you expecting?

Then, if you want to show the last two digits of the current year (e.g.,
"08"), one way to do this is to have a field in which you store a date (or
date/time) value, say, when the record was created, then use a query to get
only the last two digits.

Finally, to come up with your "number", you'd use a query with something
like:

    MyNumber: "MPL" & " " & [MySequenceNumber] & "/" &
Right(Year([YourDateField]),2)

You'll need to create your own procedure to:

    * test to see if the "08" still applies or needs to be "09"
    * check for the maximum sequence number for the applicable 'year'
    * add one

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am creating a certain program and in that program i want to add a
field which will be creating numbers with text in sequence older "
Like MPL 001/08  to unlimited " And 08 be current year. This will
onlybe possible if that field is enebled, if the condition of enebling
this field still is false then the field returns the previous value
that is it maintances the previous record number.

please assist as soon as possible- Hide quoted text -

- Show quoted text -

Thanks Jeff
I Have created a table with three fields
Like Feild Name Data type
Mycode Text
SeqNo Number
Year Date/Time

And am interested in field properties
Like Mycode
Format should be what
inputmask should be
default value should be

After this thenyou said that i should create a query and that query
must it generated from table i created if so should i create another
field in query and enter a creteria like " MyNumber: "MPL" & " " &
[MySequenceNumber] & "/" &

Right(Year([YourDateField]),2)" or replace select sql query.

Please help i have never come across this procedure in my life

But i do believe in your answer

Good day