Does this require code?

[Guest]

Hi

I am new to access and I not sure if the task I am trying to achieve
requires code or if it is a feature built into access.

I have created a number field in access and I wish to have the field
incremented by one. The starting value of the field should be 1. The
display should however be able to accomodate for digits e.g 0001. Therefore
starting at 0001, I wish to increment to 0002. How can I do this.

 

[Guest]

It depends on what you want to do with this number. Why do you want to show
a sequential number? There are certainly valid cases for doing so, but they
are rare. If this is just a key field you are trying to use to related
parent/child records, then you can use the Autnumber data type and make it
your primary key. This is a value users should never see. It is intended
for the use I described. Another drawback is that Autonumbers do not stay
sequential because of deletions, or canceling after starting a new record,
etc. Also, a Compact/Repair will get you numbering out of sequence. All the
more reason to use Autonumber for primary key fields only.

Now, if there is a really good reason to use a sequential numbering scheme,
it is not that hard to do it.

strNextNum = Format(Nz(DMax("[NumberField]", "MyTable"),0) +1, "0000")

NumberField is the field you carry the list of numbers in - Use your own
field name.

MyTable is where you put the name of the table that has the NumberField
field in it.

the DMax function finds the highest number in NumberField and adds 1 to it.

The Nz function prevents the DMax from returning a Null if there are no
numbers in the database and substitues a 0

The Format function makes sure you number will be 4 digits with leading zeros.

So, what happens when you try to add record 10,000?

 

[Guest]

Hi

My form generates trade tickets, where the number is generated automatically
using the autonumber feature. I have created a report to display the
information and tied that field to the query which is using the autonumber
information. However management requests that the number be displayed as
stated before 0001. Is there a way maybe using the input mask to have the
number displayed in the desired format. So it starts with 1 - but displays
0001.

Thanks

It depends on what you want to do with this number. Why do you want to show
a sequential number? There are certainly valid cases for doing so, but they
are rare. If this is just a key field you are trying to use to related
parent/child records, then you can use the Autnumber data type and make it
your primary key. This is a value users should never see. It is intended
for the use I described. Another drawback is that Autonumbers do not stay
sequential because of deletions, or canceling after starting a new record,
etc. Also, a Compact/Repair will get you numbering out of sequence. All the
more reason to use Autonumber for primary key fields only.

Now, if there is a really good reason to use a sequential numbering scheme,
it is not that hard to do it.

strNextNum = Format(Nz(DMax("[NumberField]", "MyTable"),0) +1, "0000")

NumberField is the field you carry the list of numbers in - Use your own
field name.

MyTable is where you put the name of the table that has the NumberField
field in it.

the DMax function finds the highest number in NumberField and adds 1 to it.

The Nz function prevents the DMax from returning a Null if there are no
numbers in the database and substitues a 0

The Format function makes sure you number will be 4 digits with leading zeros.

So, what happens when you try to add record 10,000?

Hi

I am new to access and I not sure if the task I am trying to achieve
requires code or if it is a feature built into access.

I have created a number field in access and I wish to have the field
incremented by one. The starting value of the field should be 1. The
display should however be able to accomodate for digits e.g 0001. Therefore
starting at 0001, I wish to increment to 0002. How can I do this.

 

[Rob Oldfield]

To follow on from klatuu's answer, you just store the basic value, but when
it needs to be displayed you just use
format(yournumber,"0000")

Hi

My form generates trade tickets, where the number is generated automatically
using the autonumber feature. I have created a report to display the
information and tied that field to the query which is using the autonumber
information. However management requests that the number be displayed as
stated before 0001. Is there a way maybe using the input mask to have the
number displayed in the desired format. So it starts with 1 - but displays
0001.

Thanks

It depends on what you want to do with this number. Why do you want to show
a sequential number? There are certainly valid cases for doing so, but they
are rare. If this is just a key field you are trying to use to related
parent/child records, then you can use the Autnumber data type and make it
your primary key. This is a value users should never see. It is intended
for the use I described. Another drawback is that Autonumbers do not stay
sequential because of deletions, or canceling after starting a new record,
etc. Also, a Compact/Repair will get you numbering out of sequence. All the
more reason to use Autonumber for primary key fields only.

Now, if there is a really good reason to use a sequential numbering scheme,
it is not that hard to do it.

strNextNum = Format(Nz(DMax("[NumberField]", "MyTable"),0) +1, "0000")

NumberField is the field you carry the list of numbers in - Use your own
field name.

MyTable is where you put the name of the table that has the NumberField
field in it.

the DMax function finds the highest number in NumberField and adds 1 to it.

The Nz function prevents the DMax from returning a Null if there are no
numbers in the database and substitues a 0

The Format function makes sure you number will be 4 digits with leading zeros.

So, what happens when you try to add record 10,000?

Hi

I am new to access and I not sure if the task I am trying to achieve
requires code or if it is a feature built into access.

I have created a number field in access and I wish to have the field
incremented by one. The starting value of the field should be 1. The
display should however be able to accomodate for digits e.g 0001. Therefore
starting at 0001, I wish to increment to 0002. How can I do this.

 

[Guest]

Hi

Where would format(yournumber,"0000") be tied to. Where is this information
placed.

Thank you all for responding.

To follow on from klatuu's answer, you just store the basic value, but when
it needs to be displayed you just use
format(yournumber,"0000")

Hi

My form generates trade tickets, where the number is generated automatically
using the autonumber feature. I have created a report to display the
information and tied that field to the query which is using the autonumber
information. However management requests that the number be displayed as
stated before 0001. Is there a way maybe using the input mask to have the
number displayed in the desired format. So it starts with 1 - but displays
0001.

Thanks

It depends on what you want to do with this number. Why do you want to show
a sequential number? There are certainly valid cases for doing so, but they
are rare. If this is just a key field you are trying to use to related
parent/child records, then you can use the Autnumber data type and make it
your primary key. This is a value users should never see. It is intended
for the use I described. Another drawback is that Autonumbers do not stay
sequential because of deletions, or canceling after starting a new record,
etc. Also, a Compact/Repair will get you numbering out of sequence. All the
more reason to use Autonumber for primary key fields only.

Now, if there is a really good reason to use a sequential numbering scheme,
it is not that hard to do it.

strNextNum = Format(Nz(DMax("[NumberField]", "MyTable"),0) +1, "0000")

NumberField is the field you carry the list of numbers in - Use your own
field name.

MyTable is where you put the name of the table that has the NumberField
field in it.

the DMax function finds the highest number in NumberField and adds 1 to it.

The Nz function prevents the DMax from returning a Null if there are no
numbers in the database and substitues a 0

The Format function makes sure you number will be 4 digits with leading zeros.

So, what happens when you try to add record 10,000?




:

Hi

I am new to access and I not sure if the task I am trying to achieve
requires code or if it is a feature built into access.

I have created a number field in access and I wish to have the field
incremented by one. The starting value of the field should be 1. The
display should however be able to accomodate for digits e.g 0001. Therefore
starting at 0001, I wish to increment to 0002. How can I do this.

 

[Rob Oldfield]

It can go in various places - it depends on what you're after. But the
easiest would be on a query. A calculated field something like:

PaddedCode:format([YourAutonumberField],"0000")

Hi

Where would format(yournumber,"0000") be tied to. Where is this information
placed.

Thank you all for responding.

To follow on from klatuu's answer, you just store the basic value, but when
it needs to be displayed you just use
format(yournumber,"0000")

Hi

My form generates trade tickets, where the number is generated automatically
using the autonumber feature. I have created a report to display the
information and tied that field to the query which is using the autonumber
information. However management requests that the number be displayed as
stated before 0001. Is there a way maybe using the input mask to have the
number displayed in the desired format. So it starts with 1 - but displays
0001.

Thanks

:

It depends on what you want to do with this number. Why do you want

to
show

a sequential number? There are certainly valid cases for doing so,

but
they

are rare. If this is just a key field you are trying to use to related
parent/child records, then you can use the Autnumber data type and

make
it

your primary key. This is a value users should never see. It is intended
for the use I described. Another drawback is that Autonumbers do

not
stay

sequential because of deletions, or canceling after starting a new record,
etc. Also, a Compact/Repair will get you numbering out of sequence. All the
more reason to use Autonumber for primary key fields only.

Now, if there is a really good reason to use a sequential numbering scheme,
it is not that hard to do it.

strNextNum = Format(Nz(DMax("[NumberField]", "MyTable"),0) +1, "0000")

NumberField is the field you carry the list of numbers in - Use your own
field name.

MyTable is where you put the name of the table that has the NumberField
field in it.

the DMax function finds the highest number in NumberField and adds 1

to
it.

The Nz function prevents the DMax from returning a Null if there are no
numbers in the database and substitues a 0

The Format function makes sure you number will be 4 digits with

leading
zeros.

So, what happens when you try to add record 10,000?




:

Hi

I am new to access and I not sure if the task I am trying to achieve
requires code or if it is a feature built into access.

I have created a number field in access and I wish to have the field
incremented by one. The starting value of the field should be 1. The
display should however be able to accomodate for digits e.g 0001. Therefore
starting at 0001, I wish to increment to 0002. How can I do this.

 

[Larry Linson]

Where would format(yournumber,"0000") be tied to. Where is
this information placed.

As I recall, you are printing these in a report for tickets -- you would not
use the format statement in that case, but simply set the Format property of
the Text Box in which you display the number to "0000".

And, if you are not familiar with Text Boxes and their properties, I suggest
you visit http://office.microsoft.com and take some of the online courses
there for a good basic understanding of Access, and/or get a copy of
Microsoft Access <versionnumber> Step by Step from Microsoft Press or
Microsoft Access 2003 Inside Out by John Viescas, also from Microsoft Press.

Larry Linson
Microsoft Access MVP


Larry Linson
Microsoft Access MVP