Generating an ID based on current date

[Paul Fenton]

We have a multiuser application where new client records get a file
number that's in the form Year (yyyy), Month (mm), Client (nnn), e.g.
200406005. Every month the client number resets to "1" and we
increment the month or year by one.

A table in the data file stores the next available client number and
the current month value. Every time a user opens a new record, the
program checks that stored month value against his computer's date and
if his date is > that stored, the stored value is incremented to match
his computer's month. There's additional code to deal with
December/January.

Been working fine for a couple years, but we just realized that if a
user's computer date was wrong, i.e. July instead of June, he would
increment the month from 6 to 7 and reset the client numbers back to
"1", even though we're still in the month of June. Subsequent
attempts to create a new record fail since the client numbers have
already been assigned.

I'm looking for a better way of assigning the file number that's less
dependent on the users' computer dates. Maybe it should be on the
server??


Paul Fenton
(e-mail address removed)

 

[John Vinson]

I'm looking for a better way of assigning the file number that's less
dependent on the users' computer dates. Maybe it should be on the
server??

I'd strongly suggest avoiding such "intelligent keys". Whenever you
try to store multiple discrete pieces of information in a single
field, as you are doing, you will run into this kind of problem! This
is precisely why Codd and Date decreed that relational attributes
should be atomic.

Would it perhaps make more sense to store the date *in a date field* -
where it can be searched, sorted, and if need be repaired - and simply
assign a sequential meaningless file number? You can use an autonumber
if you don't mind gaps, or (probably better) simply a sequential
number maintained in code.

 

[Paul Fenton]

John, thank you for the reply and I agree with you. However, the
client has been numbering his projects in this manner since he started
in business and demands it be done this way. Since he signs my
checks, I'm doing what he wants rather than what might make more
sense from a programming or database point of view, Codd and Date
notwithstanding.

Trust me, I argued for something simpler.


Paul Fenton

 

[John Vinson]

I'm looking for a better way of assigning the file number that's less
dependent on the users' computer dates. Maybe it should be on the
server??

The server date might be wrong too, in which case you would have
multiple users with errors, not just one. I'm afraid this is just a
GIGO situation - if the computer is provided with incorrect data it
will get incorrect results!

I'd suggest a Google Groups search for "server time". I have seen
posts about it (months ago) but I don't know how to get the server's
time. Good luck, and sorry I can't be of more help!

 

[Paul Fenton]

Thanks, John. I'll try that.

Paul Fenton

The server date might be wrong too, in which case you would have
multiple users with errors, not just one. I'm afraid this is just a
GIGO situation - if the computer is provided with incorrect data it
will get incorrect results!

I'd suggest a Google Groups search for "server time". I have seen
posts about it (months ago) but I don't know how to get the server's
time. Good luck, and sorry I can't be of more help!

 

[Douglas J. Steele]

http://www.mvps.org/access/api/api0039.htm at "The Access Web"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)