date and number

[Guest]

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

 

[strive4peace]

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
*