Auto Number - Format Help 1

[tpearo]

Can anyone help me with this one?
I have a table that has a field called RMA#. I can't use the auto number feature because of the format required by this customer.
The RMA# is a sequential number assigned to each new record. The format is ex. 4605-355, where the 46 relates to the week of the year and the 05 is the year - and the 355 indicates the record or RMA for this year. So as an example the next record in the table would be 4605-356. The other problem is that when the week or year changes then the number has to change accordingly. Ex week 47, 48 etc to 52 and year 05 - whenever. Each year this would restart with a new series of numbers.
I hope I'm clear with my request and not just babbling.

Anyones help would be appreciated.

Tp

 

[Remou]

Here is a rough idea:
[tt]Format(Format(Date, "ww"), "00") & Format(Year(Date), "yy") & "-" & DMax("Mid([RMA#],6)", "TableName") + 1[/tt]
You may like to look at:
Why AutoNumber shouldn't be used in MultiUser databases (And How to ge the Unique Number)
faq700-184