auto number

S

Stevene James

hi there,
i need to assign autonumbers as a 6 digit code but in a specific format i.e

if a customers company name is 'Any Customer' then
a) i would like the first 3 to be ANY
b) if this is the first customer with 'ANY' then 3 digits of '101'

making "ANY101".

If the next customer was called 'Any Cars' then it would be 'ANY102'...etc

whats the best way to do this.
 
S

Steve Schapel

Stevene,

I would suggest assigning this value using code on the form's Before
Update event. Something like this (untested, air code)...

Dim IDRoot As String
IDRoot = StrConv(Left(Me.CompanyName,3),1)
If Me.NewRecord Then
If DCount("*","Customers","Left([ID],3)='" & IDRoot & "'") = 0 Then
Me.ID = IDRoot & "101"
Else
Me.ID = IDRoot &
DMax("Val(Mid([ID],4))","Customers","Left([ID],3)='" & IDRoot & "'") + 1
End If
End If
 
A

av

Make the table called "customers" with whatever you like fields but the
ensure one string row (name it "str") with 3 characters for each and
additional row for the ID number (name it "id"):
You can also add the autonuber.
(ex:
"Any", 2
"Aux", 5
.... etc.)
Make the querry with selected fields "str" and "id" and for the desired
result eneter in the next free querry field the following:
" desired_result: [str]&[id] "
This can be "make table querry".

The problem is with leading zeros.
 
Top