mask on a key and like- autonumber (1 Viewer)

[vivou]

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??

 

[Jack Cowley]

Do this using two fields not one. Put the year in one field and then use code to increment the number in the second field. To show the number as 2003-0001 just concatenate the two fields with code. You could use code like this in the Control Source of an unbound field on a form: = Me.MyYearField & "-" & Me.MyNumberField.

This code placed in a forms On Current event will increment the number field:

If Me.NewRecord Then
On Error Resume Next
Me.MyNumberField = Nz(DMax("[MyNumberField]", "MyTableName"), 0) + 1
End If

hth,
Jack

 

[vivou]

i have just one little problem left :
how can i set the autonumber part as
0001
0002 ...

instead of
1
2...

?

 

[Jack Cowley]

In the fields Format property put: 0000 (that is 4 zeros). Now it will display as 0003....

hth,
Jack

 

[vivou]

it's work
but the problem is:

i want to combinate year+index as 4 digits

and when i do it i lost the the 4 digits:
ydate = DatePart("yyyy", date)

orderfullID = ydate & "-" & id_order


-----> 2002-1 and not 2002-0001

how can i do that ?
i tried:
orderfullID = ydatee & "-" & STR(id_order)
but it doesnt accept the STR() function

how can i cast the long integer to string with format "0000"???

 

[Jack Cowley]

Try:

orderfullID = ydate & "-" & Format(id_order, "0000")

hth,
Jack