ID number

[Guest]

Alright, I've got to make a database for the upcoming year which has an ID
number that changes based on several fields.

Here is a sample ID number: OCV05-0001
The "OCV" is static, it appears in every ID number.
The 05 is the year in which the specimen was collected
and the "0001" is the number of the entry in relation to the position it was
entered;for example, if the first entry for 2005 was entered, it would have
"0001" in that field.

The only solution I could think of, is something to the effect of an IIF
statement which checked the year to see if it was 05. If that is true, then
displays "OCV" & [Collection Year] & "-" (Dmin([Collection
Date],"Table",[Collection Year]=05))

However, I've tried this and it doesnt work. Can someone help a poor, Access
noob out?

This is also a completely inelegant way of getting a solution.

Is there a better way to do this? Actually, is there a way to do this that
it will work. Changing the ID number system is not an option, unfortunately.

Thanks a bunch in advance.

Brandon

 

[tina]

are you wanting to *display* a record ID based on the data saved in three
fields in the record? or are you wanting to *create* a record ID based on
the three data elements you specified, and *assign* that ID to each new
record?

 

[Guest]

For your data entry form have an unbound listbox with record source query SQL
--
SELECT "OCV" & Right(DatePart("yyyy",Date()),2) & "-" & Right("000" &
Max(Val(Right([ID],4)))+1,4) AS NextIDNumber
FROM YourTable;
Set visible property to to NO.
Make the default for your ID textbox field --
=[Forms]![YorForm]![YourList]

 

[Guest]

I guess I should have been clearer, I'd like to record this number but the
bet Icould do was figure a way to display it.

I dont know much about SQL, so I'll look up how to go about entering these
commands. Thanks for the response. I'll try it out tomorrow when I head back
to work.

Brandon

 

[tina]

well, unless you intend to use the "combined" value as the record's primary
key, suggest you store the values in separate fields in the table, subject
to the following notes:

1. recommend you store the "OCV" as hard data in each record, unless there
is absolutely no possibility of that prefix *ever* changing OR unless you're
absolutely sure that you would want a prefix change to be applied to all
records regardless of how long ago they were created.

2. if you have a date field in your table which stores a "date stamp" when
the record is created, then you already have access to the year and don't
need to store it again. you can display the year of the record as needed
with the following expression in a query, form, or report, as
Format(Year(MyDateField), "yy")

3. the entry number (0001, 0002, etc) has to be generated programmatically
as each record is entered. this can be tricky in a multi-user database;
suggest you read up on those issues.

when storing the three data elements in separate fields, you avoid
duplicating existing data. it's easy to concatenate the three fields
together whenever you need to display them.

if you *do* intend for this ID to be the table's primary key, concatenate
the three values as you would to display them, then use the expression in a
macro or VBA to set the value of the ID field in the form. due to the issues
in a multiuser database, suggest you run the macro or VBA code in the form's
BeforeUpdate event. if you need more specific instructions, you'll need to
provide more specific information about your form and what you're doing in
it.

hth