mask on a key

[virginie]

hi!
i have a unique key in a table
that is an index like this :

yyyy-9999
this has to year+"-"+number id as 4 digits

how can i force the field to be like this??

and how it will be in the forms?

how can i add it as automatic number
2003-0001
2003-0002
...
can someone help??

 

[Darrylles]

Hiya,

Use the 'DatePart' function to extract the year from the system date to get the Year portion of your id.

As for the numeric part, I'd have a new table with one field: type integer. Also make it the key. (Call the table tblAutoNum, and the field LastID (for future reference)).

When you need a new ID, use an 'hidden' form that automatically assembles your ID from the DatePart function and the value + 1 from the tblAutoNum.

E.g. The form will display the tblAutoNum.LastID value.
Add 1 to it and save it (for next time).
Use a global (public) variable to hold the new id:

strMyID = DatePart("yyyy",[OrderDate]) & "_" & str(LastID))

(Define or DIM strMyID in a module).

Close the form.

In your current form with the field you wish the id to be in:

myfield = strMyID

Done.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[virginie]

this works, actually!

but the little thing left is that i get this following result":

2002-1
2002-2
...

how can i set the number to be -0001 ??
in your sentence :
strMyID = DatePart("yyyy",[OrderDate]) & "_" & str(LastID))

it works omly if i delete the str before lastID
strMyID = DatePart("yyyy",[OrderDate]) & "_" & (LastID))
otherwise it is error...
why?