Here is the problem.
I have a primary key for a database that is 2 fields.
contract_id and Contract_date
now I want the internal id number to be the a string of
contract_date + contract_id and i want to store that in a
field called internal_id.
Well... No. You really do NOT want to store that field. It's redundant
and it violates first normal form. A date IS DATA, and should be
treated as data; and if the Contract_ID is already unique, then
jamming it together with a date doesn't make it any more unique.
now contract_id is an autonumber field and contract_date
has a default value of the current day's date.
does anyone know how to do this?
Autonumbers aren't really designed for this purpose. The autonumber
value will increment on and on; it will develop gaps, not only when
you delete a record but even when you hit <Esc> while adding a record;
if you run an Append query you may get a gap of thousands of numbers
in the sequence. I would *not* recommend using an Autonumber if
anyone's going to look at the value!
Instead, assign it in code. If you want the number to start at 1 and
increment forever, use a Form and put code like this in the Form's
BeforeInsert event:
Private Sub Form_BeforeInsert(Cancel as Integer)
Me![ContractID] = NZ(DMax("[ContractID]", "[yourtablename]")) + 1
End Sub
If you want the ContractID to start over with 1 every day, put a
criterion on the DMax:
Private Sub Form_BeforeInsert(Cancel as Integer)
Me![ContractID] = NZ(DMax("[Contract_ID]", "[yourtablename]",
"[Contract_Date] = #" & Date() & "#")) + 1
End Sub
You can concatenate the two fields for display or printing by using a
Textbox with a control source of
=[Contract_Date] & Format([Contract_ID], "0000")
to display (for example) 01/16/040000. Adjust the Format and the
expression to display however you like... but *don't* store this in
your table.
