asked on

Looking for custom autonumber

I am trying to create a custom autonumber field for a database. What I really want is a five digit number followed by the year.

So something like 00001-08

Tried working with something like this

Format(Now(), "YY") & DMAX("myField", "myTable") + 1

Not much luck. One of the problems I think I will nedd to address is how to restart the nunbers at 00001 for 2009. So I would have 00001-09

antontolentino

use this function as control source of your control

Function RandomSerial()
Dim myYear As Integer, RandomNum As Long
    RandomNum = Int((99999 * Rnd) + 9999)
    myYear = Int(Year(Now()))
    RandomSerial = Str(RandomNum) & "-" & myYear
End Function

Open in new window

antontolentino

sorry... the RandomNum should be...

RandomNum = Int((90000 * Rnd) + 9999)

to keep the 5 digit limitation

antontolentino

The above will give the full year like 12345-2008

if you want to have 12345-08 use this one

Function RandomSerial()
Dim myYear As Variant, RandomNum As Long
    RandomNum = Int((99999 * Rnd) + 9999)
    myYear = Int(Date)
    RandomSerial = Str(RandomNum) & "-" & Right((myYear), 2)
End Function

Open in new window

wiswalld

ASKER

Where are you putting this? Looks good to me.

wiswalld

ASKER

number bouncing all over the place.

antontolentino

sorry, i thought you want random 5 digit autonumber

try this one.

=Format(Str(DCount("[ContactID]","Contacts","[ContactID] > 0")+1),"00000") & "-" & Right((Date()),2)

put that in the ControlSource property of the textbox control

antontolentino

that assumes that you have a blank table name Contacts with ContactID as KeyField

wiswalld

ASKER

OK for the first records works great but I get "error" in the field if I try to add a new record. I have put this in the default value as it doesn't work at all in the control source for the field.

antontolentino

if your db is not too big and you don't mind attaching it it would be better. actual table details would help if you cant attached the file

wiswalld

ASKER

I just through one together to get an idea.

wiswalld

ASKER

Here it is
db1.txt

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wiswalld

ASKER

Cap

Exactly what I need. Thanks.

wiswalld

ASKER

This is what Capricorn1 gave me.

Function getNextNo() As String
Dim intMax As Integer, curVal, newVal, yr As String
yr = Right(Year(Date), 2)
curVal = Nz(DMax("RandomNum", "Table1"))
If Right(curVal, 2) = yr Then
If Len(curVal & "") > 0 Then
intMax = Left(curVal, 5)
newVal = Format(intMax + 1, "00000") & "-" & yr
Else
newVal = "00001" & "-" & yr
End If
Else
newVal = "00001" & "-" & yr
End If
getNextNo = newVal
End Function