Creating a unique Case #

[BLW]

My case #'s are in the following format:

Type of case(2-3 letter code)-Year(2 digit year)-Numer(count of these types
of cliams for the year)

How do I get the form to automatically fill in the case# field? I have a
field for the date of the event and the type of case. I guess I would want
it to automatically count how many of these types of cases we have had this
year and add one to it.

I am stumpped. Any help would be appreciated.

 

[Cheese_whiz]

Hi BLW,

If you want a 'case number' that you use internally for refering to records
and you want it to be based at least in part on certain aspects of the case
(year, case type), then I suggest you use a calculated field in a query. You
can add the three components you want to produce the results for that
calculated field but you don't have to store it in a table (you just store
the components that go into it individually, like they SHOULD be stored).

As for the third component....the part based on counting the existing
records of that type and adding one, that won't work. If you delete a record
that isn't the last added record of that case type, your count (+1) will give
you a number that's already used. In any event, the number of the last
entered case is NOT going to be an accurate count of the records, so no sense
in trying to make it one. You can always just do a programmatic count of the
records if/when you need it.

If you do it your way (or a 'modified' your way that works), you are either
going to end up storing duplicate data (the individual components of that
make up the caseID plus the caseID itself) OR you are going to have to go
through unnecessary steps to pull out the individual components from the
combined field if, for example, you wanted to do something like filter cases
based on case type.

Either way, it's a bad deal, imo.

For those reasons, I'd just use the autonumber field to give you a unique
primary key for your table, and then use that table in a query and add a
calculated field IF you want to display the combination of fields as a kind
of 'case number' to use to refer to cases within your office, or even to use
to search for cases in your database.

Just my opinion.
CW

 

[Dennis]

I use a table that has a single column in it, called "NextAvailableID". I use
a 3-charater alpah field to maintain the leading zeroes, but convert to a
numeric when I'd adding one to the number. Then I format it as an alpha again
(leading zeroes) and update the record.

In my case the number cycles on a daily basis. I reset it at midnight, so
that the next incident gets a "001" (i.e. "20080910001" = yyyymmddxxx, where
xxx is the incident number).

Works like a charm. I had to code it in VB though. The incident number is
only created when the user clicks the "Post New Record" button. (The form is
unbound, and they enter data for a new record and then click a command button
to write the new record. Part of that command's code assigns the number.)