Auto Numbering

[Mavis]

Hi all,

I have this table named "TBLDamagesDetail", The primary key of this table is
"DamageID". It act as a auto number. I will like to change the format of the
"DamageID" to 09-0001. 09 will be the currenct year and 0001 will be the
running number.
Can someone advice how to write the code for this?

Thanks!

 

[Steve Schapel]

Mavis,

Do you happen to know Charlie?

You will need to change it to a Text data type. I do not know what you
intend to do with the existing records.

So there will never be more than 9999 Damage records in a year?

I would write a user defined finction for this. In a standard module,
something like this (totally off the top of the head and not tested!)...
Code:
--------------------------------------------------------------------------------

Public Function NextDamage() As String
Dim NextThisYear As Integer
NextThisYear =
Val(DMax("Mid([DamageID],4)","TBLDamagesDetail","Left([DamageID],2)=Format(Date()),'yy')"))
+ 1
NextDamage = Format(Date(),"yy") & "-" & NextThisYear
End Function
--------------------------------------------------------------------------------
And then you can use this function by putting:
=NextDamage()
.... in the Default Value setting for the DamageID textbox on your form.

Well, that's one way of doing it.

 

[Steve Schapel]

By the way, Mavis, I hope you will understand where I am coming from here,
as no offence is intended... But I have noticed you often posting exactly
the same question to other forums, at the same time. No law against that, of
course, but it would be good for you to have some compassion for the
volunteers who spend time helping others. To be honest, it irks a little to
spend half an hour that I could be spending with my daughter, putting
together an answer to help someone, only to find that someone else in
another place has been doing the same, unbeknown to each other. In practice,
it is probably rarely necessary to duplicate your question. My suggestion is
to give it a go in one forum first, and only go fishing elsewhere if you
haven't found anyone to give you good help after a decent amount of time,
like 24 hours. Either that, or at least announce "cross posting to Utter
Access" or whatever. Just my 2c. Thanks.