Custom input mask?

[jazminecat23]

hi all, I need to auto number the records my users enter into a form. i've created it as a text field, but can change that if I need to. I need them to follow the following format

YY = last 2 digits of the current year
MM = two digit month
nnnn = auto numbering starting at 0001 at the beginning of every month.

So, for example, the third record created in October of 2007 will be 07100001

and the 45th record created in october of 07 will be

07450001

Can anyone tell me how to do this?

 

[lameid]

Honestly this sounds like horrible design but here it goes...

I would have at least 2 fields to make up this type of 'key'.

Also your example data did not make much sense so I am assuming YYMMnnnn where n is the 'auto' number.

First store a field with YYMM (fieldDate). Then in the second store the number as a number (long integer; fieldLNG).

Find the next number with somthing like...

NZ(DMAX("fieldLNG", "Table", "fieldDate = """ & Format(date(),"yymm") & """"),0) + 1

Finally when you have to display this text...

fieldA & FillLeft(fieldLNG, "0", 4)

Just be sure to include the below in a module...

Function FillLeft(strIN As String, strFillString As String, lngFillLengthTo As Long) As String
    Dim lngNumberCharactersNeeded As Long
    Dim lngCount As Long
    Dim strTemp As String
    lngNumberCharactersNeeded = lngFillLengthTo - Len(strIN)
    lngCount = 0
    While lngCount < lngNumberCharactersNeeded
        lngCount = lngCount + 1
        strTemp = strTemp & strFillString
    Wend
    FillLeft = strTemp & strIN
End Function

Strongly consider using a real autonumber for your key.

 

[jazminecat23]

Hi lame - you're right, my example was flawed. It was a long week...

It should be:

So, for example, the third record created in October of 2007 will be 07100003

and the 45th record created in october of 07 will be

07100045

and then the 45th record in november of 07 would be

07110045

I know, I know. But this is how the client wants to number their cases. I have tried to understand why they want to do this to no avail.

Will your example do that. And yea, I have built in a real autonumbering that doesn't use any such hooha but is a simple autonumber, as the primary key, just to make sure things don't get all messed up.

Thank you so much for your response and for helping me!

 

[lameid]

My example gives you the pieces to make the case number. Although I would just use this as a faux case number for the customer and use the real primary key through the database... Then when they decide to change 'Case Numbers' it is not an update nightmare.

Good luck.