Re: AUTONUMBER- but with a custom alphanumeric

Tech-Archive recommends: Fix windows errors by optimizing your registry

"=?Utf-8?B?TW9ybmluZ1N0YXJGYW4=?="
<MorningStarFan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
news:A5689E3F-784E-4A89-82E4-0FF1D24DA837@xxxxxxxxxxxxx:

> These projects will have an alphanumeric id which will something
> similar to 2005DR1, 2005DR2, 2005DR3, then 2005DP1, 2005DP2, etc.

This looks suspiciously like two or three separate piece of information.

If the 2005 refers to a year, then it should be in an integer field
called YearNumber.

If the DR is always DR, then it should not be in the database at all. If
it means something and will sometimes change, then it should be in a
small text field called something like TwoLetterDepartmentCode.

If the number has to recycle with every new YearNumber and
TwoLetterDepartmentCode, then you can use a plain integer (or long) but
you'll need some code to allocate it safely.

It's trivial to find a record, as in

SELECT Something FROM Somewhere
WHERE YearNumber = 2005
AND TwoLetterDepartmentCode = "DR"
AND SerialNumber = 003


and if you ever need to

GROUP BY TwoLetterDepartmentCode

then you'll be stuffed if you have stuffed it into the middle of an
"intelligent key". Read up on First Normal Form for more details.

For presentation to the user, you create a control on the form or report
and point its ControlSource to

= YearNumber & TwoLetterDepartmentCode & Format(SerialNumber,"000")

Hope that helps



Tim F

.

Quantcast