Client Id

[Guest]

hi everyonei
i have a field clientID where i want to generate the clientId using the
following format; today's date, then a dash, then x and four numbers.
for example; ClientId 201105-x1000
ClientID 201105-x1001
are 2 successive clients. i can presently extract today's date, but having
problems with the rest. also i want this ClientId to be permanent; that is if
the cleint's name is updated another day i don't want the Clientid to take
the date on which the update was made.
thanks.

 

[Rick Brandt]

hi everyonei
i have a field clientID where i want to generate the clientId using
the following format; today's date, then a dash, then x and four
numbers.
for example; ClientId 201105-x1000
ClientID 201105-x1001
are 2 successive clients. i can presently extract today's date, but
having problems with the rest. also i want this ClientId to be
permanent; that is if the cleint's name is updated another day i
don't want the Clientid to take the date on which the update was made.
thanks.

You are much better off actually storing this in two separate fields. You can
then use an expression on forms and reports to *display* it in the format you
are describing. Then all you need is a CreatedOn date field with a default
value of Date() and an ID number field that you increment for each record.

The display expression would be...

=Format([CreatedOn], "ddmmyy") & "-x" & [ID]

 

[David C. Holley]

The following statement

?Right(Year(Now), 2) & Format(DatePart("y", Now), "000") & Int(Timer)

will create a record ID that imbeds the year and date and time that the
record was created. The first two digits represent the year [05], the
next three the Julian date [324] with the remaining digits representing
the approximate number of seconds past midnight when the record was
created [36374]. Using this method will result in holes in the sequence
if you compare the ID's for the records created on the same date as in

0532436359
0532436374
0532436856

but they will be sequential (as opposed to consecutive). You will find
that it is very seldom (if ever) that you need to know information such
as record X was the 5th of 25 records added. If client X was created
before or after client Y, then its just a simple comparison. But again,
its very seldom (if ever) that you'll ever need to know this.

If you're working in a multi-user environment, there is the possibility
that a user ID will already exist - assuming that two users try to
create a record at the *exact* time, however the probablity of this is
unlikey. Additionally, it is possible to code for this whereby you check
to see if the ID already exists and then just try again. (The number
would be altered on each subsequent try as the Timer() will have
incremented.)

David H

hi everyonei
i have a field clientID where i want to generate the clientId using
the following format; today's date, then a dash, then x and four
numbers.
for example; ClientId 201105-x1000
ClientID 201105-x1001
are 2 successive clients. i can presently extract today's date, but
having problems with the rest. also i want this ClientId to be
permanent; that is if the cleint's name is updated another day i
don't want the Clientid to take the date on which the update was made.
thanks.

You are much better off actually storing this in two separate fields. You can
then use an expression on forms and reports to *display* it in the format you
are describing. Then all you need is a CreatedOn date field with a default
value of Date() and an ID number field that you increment for each record.

The display expression would be...

=Format([CreatedOn], "ddmmyy") & "-x" & [ID]

 

[Tim Ferguson]

The following statement

?Right(Year(Now), 2) & Format(DatePart("y", Now), "000") & Int(Timer)

will create a record ID that imbeds the year and date and time that the
record was created.

True, but that does not stop it from being a Really Bad Idea...


Tim F

 

[David C. Holley]

And it would be a really bad idea because of why?

 

[Rick Brandt]

And it would be a really bad idea because of why?

Generally it is a bad idea to embed multiple pieces of data in a single field.
Such "smart keys" as Right(Year(Now), 2) & Format(DatePart("y", Now), "000") &
Int(Timer) violate this rule.

Doesn't mean that a whole lot of databases don't do this though.

 

[David C. Holley]

But are you assuming that the data is being extracted from the key on a
regular basis? The original idea behind the key (years and years ago)
was to create a more meaningful key as opposed to 1, 552, 3425, etc.
Plus, while I wouldn't advocate putting every piece of information about
the record into the key field, is there really any harm in embedding the
date/time of record creation into the key and then later extracting it
if needed?

 

[Rick Brandt]

But are you assuming that the data is being extracted from the key on
a regular basis? The original idea behind the key (years and years
ago) was to create a more meaningful key as opposed to 1, 552, 3425,
etc. Plus, while I wouldn't advocate putting every piece of
information about the record into the key field, is there really any
harm in embedding the date/time of record creation into the key and
then later extracting it if needed?

Harm? I doubt it. But a relational purist will still argue that you could put
all of those "pieces" of data into individual fields and make them a composite
key. The "all-in-one" value could then be displayed on forms and reports
without being an actual monolithic field for the purpose of a record identifier.

 

[David C. Holley]

Damn the purists! Those theoretical abstractions are worthless in the
real world! Burn them all and for that matter the lawyers & politicians
with them and toss in Bill Gates for good measure! (Not that I'm a
religious fanatic or anything)

Back to the real world, my thing about normalization (and thus
relational purist) is that I feel that it should always be secondary to
specific business requirements. When there's not a specific business
requirement, then good decision making, based on a variety of
considerations, should step in.

David H