Primary Key format

[Blakey300]

Hi

I am using Access 2007

I have an installation form which has an autonumber Primary Key
[InstallationID]

I have set the fields format property to 00000 so it displays as a 5 digit
number with leading zeros.

I also have an [installation Code] field on this form (This is a 2 charecter
Prefix eg:AC)

What I am trying to achive with an additional field is =[Installation Code]
& (InstallationID] to return a value of "AC00001" but no matter what I try, I
only get "AC1"(drops the leading zeros on [InstallationID])

Does anyone have any ideas???


Many thanks

Dave

 

[Allen Browne]

Set the Control Source of your other text box to:
=[Installation Code] & Format([InstallationID], "00000")

 

[Jeff Boyce]

If it is your intent that the autonumber-derived value ([InstallationID)] is
sequential, you're in for a rude shock. Access Autonumbers are designed to
provide unique row identifiers, are not guaranteed to be sequential, and are
generally unfit for human consumption.

If you persist in using the autonumber-generated value (see Allen's response
for how), you will eventually end up with non-sequential values. Will this
be a problem?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Blakey300]

Jeff

Autonumber is fine as it is only to ensure that each Installation has a
unique referance number, as such it wont actually matter if there are gaps in
the autonumber.

I do have another question though, this is in the same DB but different
section but it kind of relates to my orignal question.

I have a field [CustomerName] what I want to do is set up another field on
my form to "pickup" the 1st 5 charecters of the above field[CustomerPrefix].
I will then set up a field along the lines of
=[CustomerPrefix]&Format([CustomerID], "000"). I will again be keeping an
autonumber so that every field is uniqe.

If it is your intent that the autonumber-derived value ([InstallationID)] is
sequential, you're in for a rude shock. Access Autonumbers are designed to
provide unique row identifiers, are not guaranteed to be sequential, and are
generally unfit for human consumption.

If you persist in using the autonumber-generated value (see Allen's response
for how), you will eventually end up with non-sequential values. Will this
be a problem?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi

I am using Access 2007

I have an installation form which has an autonumber Primary Key
[InstallationID]

I have set the fields format property to 00000 so it displays as a 5 digit
number with leading zeros.

I also have an [installation Code] field on this form (This is a 2
charecter
Prefix eg:AC)

What I am trying to achive with an additional field is =[Installation
Code]
& (InstallationID] to return a value of "AC00001" but no matter what I
try, I
only get "AC1"(drops the leading zeros on [InstallationID])

Does anyone have any ideas???


Many thanks

Dave

 

[Jeff Boyce]

I'm not sure what the question is, but I'd use a query to generate that
string. I'd use Left([CustomerName],5) & Format(...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff

Autonumber is fine as it is only to ensure that each Installation has a
unique referance number, as such it wont actually matter if there are gaps
in
the autonumber.

I do have another question though, this is in the same DB but different
section but it kind of relates to my orignal question.

I have a field [CustomerName] what I want to do is set up another field on
my form to "pickup" the 1st 5 charecters of the above
field[CustomerPrefix].
I will then set up a field along the lines of
=[CustomerPrefix]&Format([CustomerID], "000"). I will again be keeping an
autonumber so that every field is uniqe.

If it is your intent that the autonumber-derived value ([InstallationID)]
is
sequential, you're in for a rude shock. Access Autonumbers are designed
to
provide unique row identifiers, are not guaranteed to be sequential, and
are
generally unfit for human consumption.

If you persist in using the autonumber-generated value (see Allen's
response
for how), you will eventually end up with non-sequential values. Will
this
be a problem?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi

I am using Access 2007

I have an installation form which has an autonumber Primary Key
[InstallationID]

I have set the fields format property to 00000 so it displays as a 5
digit
number with leading zeros.

I also have an [installation Code] field on this form (This is a 2
charecter
Prefix eg:AC)

What I am trying to achive with an additional field is =[Installation
Code]
& (InstallationID] to return a value of "AC00001" but no matter what I
try, I
only get "AC1"(drops the leading zeros on [InstallationID])

Does anyone have any ideas???


Many thanks

Dave

 

[Blakey300]

Thats perfect

thank you very much for your help, and I wish you a very pleasent upcoming
festive holiday.

Regards

Dave

I'm not sure what the question is, but I'd use a query to generate that
string. I'd use Left([CustomerName],5) & Format(...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff

Autonumber is fine as it is only to ensure that each Installation has a
unique referance number, as such it wont actually matter if there are gaps
in
the autonumber.

I do have another question though, this is in the same DB but different
section but it kind of relates to my orignal question.

I have a field [CustomerName] what I want to do is set up another field on
my form to "pickup" the 1st 5 charecters of the above
field[CustomerPrefix].
I will then set up a field along the lines of
=[CustomerPrefix]&Format([CustomerID], "000"). I will again be keeping an
autonumber so that every field is uniqe.

If it is your intent that the autonumber-derived value ([InstallationID)]
is
sequential, you're in for a rude shock. Access Autonumbers are designed
to
provide unique row identifiers, are not guaranteed to be sequential, and
are
generally unfit for human consumption.

If you persist in using the autonumber-generated value (see Allen's
response
for how), you will eventually end up with non-sequential values. Will
this
be a problem?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi

I am using Access 2007

I have an installation form which has an autonumber Primary Key
[InstallationID]

I have set the fields format property to 00000 so it displays as a 5
digit
number with leading zeros.

I also have an [installation Code] field on this form (This is a 2
charecter
Prefix eg:AC)

What I am trying to achive with an additional field is =[Installation
Code]
& (InstallationID] to return a value of "AC00001" but no matter what I
try, I
only get "AC1"(drops the leading zeros on [InstallationID])

Does anyone have any ideas???


Many thanks

Dave