date and number

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top