I have a form (called company)that I need a custom autonumber added. On the form I need my company number field to combine [edc]+ date and increment by 1 for each new record. EDC field is entered by user. No user has the same edc number.

This is the idea I have:

= DMax([edc],"Forms![Company]![edc]"," " & date) + 1


Company field would read 4160125021
edc=416
date=01/25/02
number=1 (counter)

Pls help.

It would be easier to add an Autonumber field to your table eg AutoID
then calculate a new field on the form eg in the controlsource of an unbound textbox

= [edc] & left(date,2) & mid(format(Date,"ddMMyy"),4,2) & right(date,2) & AutoID

This should give you the custom field you are looking for.
You could also store this field if you wanted to but since it can be calculated, there is no need to.
HTH

This calculation works good.

But I need it to store back to my company # field. If I use autonumber and delete a record, numbers will be missing.

How can I alter this to store back to my table??


Thanks.
Z

Even if you store and delete, you will still have numbers missing. Say you create 10 companies but then delete any of them (including the last). The next autonumber will still be 11 and will not move down the others. Is it that important to have sequential numbering?
To store the field, create a bound textbox on your form to the field in which you want to store it in then set the default value to the above formula. When a new record is created, so will be the ID.
HTH