auto number

[Guest]

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.

 

[Rick Brandt]

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.

Standard AutoNumber advice...

If you care about the value in ANY respect other than uniqueness, then don't
use an AutoNumber.

 

[Guest]

ok but can access display this automatically based on the field

 

[Joseph Meehan]

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.

Chances are autonumber is not the best solution, you likely will want to
generate your own numbers. Autonumber may not always give you consecutive
or contiguous numbers.

Before anyone can answer your next question we will need to know what
would be the third entry if it were '' Old Customer'' Would it be OLD101 or
OLD103 (Why do you want to start with 101 and not 001?)

 

[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

 

[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.