AutoNumber Help

[Mike M]

All,

I would like to create a field in my form that uses an
autonumber, but has a format of MMDDYYYY-xxxx where;
MM = 2 digit month
DD = 2 digit day
YY = 4 digit year
xxxx = unique number (autonumber)

However, I would like to see if the last part (xxxx) can
be reset back to 1 each new day.

TIA
Mike

 

[Marshall Barton]

I would like to create a field in my form that uses an
autonumber, but has a format of MMDDYYYY-xxxx where;
MM = 2 digit month
DD = 2 digit day
YY = 4 digit year
xxxx = unique number (autonumber)

However, I would like to see if the last part (xxxx) can
be reset back to 1 each new day.

This should be two fields in the form's record source table,
one for the date and the other for the sequential number.
The two values can be formatted and concatenated any way you
like at the time you want to display them to the user.

For a single user system, the code in the form's
BeforeInsert event procedure could look like:

Me.numberfield = NZ(DLookup("numberfield", "thetable", _
"datefield = " & Me.datefield), 0) + 1

Then the text box that displays the thing you want the users
to see can use an expression like:

=Format(datefield, "mm\/dd\/yyyy\-") & Format(numberfield,
"0000")

 

[Tim Ferguson]

I would like to create a field in my form that uses an
autonumber, but has a format of MMDDYYYY-xxxx where;

Just to amplify Marshall's comments, I would strongly recommend storing the
date part as a DateTime field. The huge disadvantage of a MMDDYYYY text or
number is that it won't sort in order without manipulating it back to
YYYYMMDD or all the way back to the original date.

Just a thought


Tim F