i am developing a database one of the felid i need it to be YY-MM-XXX
year then the month and sqe.No such as 2007-01-0001, how can i create it

the field must be auto generate once the form open to enter the data

thanx in advance for

Hi kawther,

you can use the form BeforeInsert event if the data is already known --
or the control AfterUpdate event if the data is only known after a
control is filled out

'~~~~~~~~~~~~~~~~`
dim mMask as string _
, mNextNumber as long

mMask = format(someDate, "yyyy-mm")

mNextNumber = nz( _
dMax( _
"mid([fieldname], 9, len([fieldname])-8)" +
,"[tablename]" _
, "left([fieldname], 7) = '" & mMask & "'" _
),0)

me.controlname = mMask & format(mNextNumber, "0000")

'~~~~~~~~~~~~~~~~

where

-- someDate is a fieldname or a controlname or a variable -->

ie:
me.date_controlname
mDateVariable

-- [fieldname] is the name of the fieldname to lookup
(store as text, length = 12)

-- [tablename] is the name of the table with the data

~~~~

even though you said the mask was YY-MM-XXX, I used your data example
with mask --> yyyy-mm-9999


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



kawther wrote:
> i am developing a database one of the felid i need it to be YY-MM-XXX
> year then the month and sqe.No such as 2007-01-0001, how can i create it
>
> the field must be auto generate once the form open to enter the data
>
> thanx in advance for