Hi

I need a auto-number field but with a letter prefix like "P1", "P2" etc.
What is the way to achieve this? Is it possible to do this at table level?

Thanks

Regards

John wrote:
[color=blue]
> Hi
>
> I need a auto-number field but with a letter prefix like "P1", "P2" etc.
> What is the way to achieve this? Is it possible to do this at table level?
>
> Thanks
>
> Regards
>
>[/color]
What type is an autonumber? In my Access, it's a LongInt.

You could have a field called ID. To Update it, you could enter
Me.ID = NZ(Dmax("ID","TableName"),0)
Then assign the letter to another field.

You could create a query to concantenate the two to make a Key with the
prefix.

"John" <John@nospam.infovis.co.uk> wrote in
news:qNudnVVOHKIRso_ZRVny2A@pipex.net:
[color=blue]
> I need a auto-number field but with a letter prefix like "P1",
> "P2" etc. What is the way to achieve this? Is it possible to do
> this at table level?[/color]

What you really want is two fields acting as a compound key, one an
Autonumber, and the other the appropriate letter.

That is, if you need the Autonumber's to be globally unique. If you
want them to have independent runs for each letter, then you have to
use a long integer and assign it programatically for the subset of
values with the particular letter in the other field.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On Sat, 11 Mar 2006 21:30:00 GMT, salad <oil@vinegar.com> wrote:
[color=blue]
>John wrote:
>[color=green]
>> Hi
>>
>> I need a auto-number field but with a letter prefix like "P1", "P2" etc.
>> What is the way to achieve this? Is it possible to do this at table level?
>>
>> Thanks
>>
>> Regards
>>
>>[/color]
>What type is an autonumber? In my Access, it's a LongInt.
>
>You could have a field called ID. To Update it, you could enter
> Me.ID = NZ(Dmax("ID","TableName"),0)
>Then assign the letter to another field.
>
>You could create a query to concantenate the two to make a Key with the
>prefix.[/color]

Small typo Salad ;) it should be -

Me.ID = NZ(Dmax("ID","TableName"),0) + 1


Wayne Gillespie
Gosford NSW Australia

Wayne Gillespie wrote:[color=blue]
> On Sat, 11 Mar 2006 21:30:00 GMT, salad <oil@vinegar.com> wrote:
>
>[color=green]
>>John wrote:
>>
>>[color=darkred]
>>>Hi
>>>
>>>I need a auto-number field but with a letter prefix like "P1", "P2" etc.
>>>What is the way to achieve this? Is it possible to do this at table level?
>>>
>>>Thanks
>>>
>>>Regards
>>>
>>>[/color]
>>
>>What type is an autonumber? In my Access, it's a LongInt.
>>
>>You could have a field called ID. To Update it, you could enter
>> Me.ID = NZ(Dmax("ID","TableName"),0)
>>Then assign the letter to another field.
>>
>>You could create a query to concantenate the two to make a Key with the
>>prefix.[/color]
>
>
> Small typo Salad ;) it should be -
>
> Me.ID = NZ(Dmax("ID","TableName"),0) + 1[/color]

Good catch! It makes sense to have an ID number that is incremented by
1 when added.
[color=blue]
>
>
> Wayne Gillespie
> Gosford NSW Australia[/color]

Wouldn't that be Me.ID = "P" & NZ(Dmax("ID","TableName"),0)+1

If you want certain number of zeros then you can:

Me.ID = "P" & left$("0000000000" & NZ(Dmax("ID","TableName"),0)+1,8)

This would create a 9 character string P00000001, P00000002,
P00000003, . . . PP00000010, P00000011, etc

"Dean" <d@coveyaccounting.com> wrote in
news:1142207618.275465.42360@j52g2000cwj.googlegro ups.com:
[color=blue]
> Wouldn't that be Me.ID = "P" & NZ(Dmax("ID","TableName"),0)+1
>
> If you want certain number of zeros then you can:
>
> Me.ID = "P" & left$("0000000000" &
> NZ(Dmax("ID","TableName"),0)+1,8)
>
> This would create a 9 character string P00000001, P00000002,
> P00000003, . . . PP00000010, P00000011, etc[/color]

You're missing the point.

First off, you shouldn't mix two pieces of information that are
independently meaningful in a single field.

Second, there are two possibilities:

1. global ID sequence, e.g., P0001, X0002, P0003...

2. ID sequence for each letter, e.g., P0001, X0001, P0002...

The former can be done with an Autonumber field plus a letter field,
while the latter cannot.

My main point is that I'd never store both letter and number in a
single field. If you never actually search on the initial letter,
then why is it there in the first place? If you do, then performance
will be vastly improved by using two fields with a compound unique
index.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Dave, I agree with you 100%. The original author did not indicated if
the two pieces of information are independently meaningful. What I was
showing was another possibility that expanded on Salad's and Wayne's
posts. There is probably more to the story than the original post.

The letter can be in a separate field.

Because the underlying table field is text DMax("[YourField]","YourTable"),
as far as I can tell, brings say 999 as the largest previous number instead
of say 1020 (text comparison instead of numeric comparison)and then adds one
to it thus creating a duplicate id 1000 which is already in the table.

Regards

"Dean" <d@coveyaccounting.com> wrote in message
news:1142216001.551659.321220@p10g2000cwp.googlegr oups.com...[color=blue]
> Dave, I agree with you 100%. The original author did not indicated if
> the two pieces of information are independently meaningful. What I was
> showing was another possibility that expanded on Salad's and Wayne's
> posts. There is probably more to the story than the original post.
>[/color]