adding name /creating field/query?

Hello,

I can create an invoice_number field in a query using the primary
field ID from the main table as

invoice_number: ID

but if ID say is 100, I cannot work out how to create

renewal_invoice_100

Cheers

Geoff

0
Geoff
12/1/2007 11:46:16 AM
access 16762 articles. 3 followers. Follow

7 Replies
708 Views

Similar Articles

[PageSpeed] 10

Geoff

We aren't there.  We can't see what you're looking at.

Where did "renewal_invoice_100" come from and what does it mean?

Please post the SQL statement of the query you are trying to use.

-- 
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

"Geoff Cox >" <<> wrote in message
news:16i2l3dor7b3ma0hem6aql215jjnf04ekp@4ax.com...
> Hello,
>
> I can create an invoice_number field in a query using the primary
> field ID from the main table as
>
> invoice_number: ID
>
> but if ID say is 100, I cannot work out how to create
>
> renewal_invoice_100
>
> Cheers
>
> Geoff
>

0
Jeff
12/1/2007 1:55:26 PM
On Sat, 01 Dec 2007 11:46:16 +0000, Geoff Cox <<>> wrote:

>Hello,
>
>I can create an invoice_number field in a query using the primary
>field ID from the main table as
>
>invoice_number: ID

If the primary key is an Autonumber, do be aware that there will be gaps in
the numbering - any invoice that's deleted will leave a gap; even hitting
<Esc> after starting an invoice will leave  a gap; adding invoices using an
Append query may leave a gap, often a huge one; replicating the database will
make your invoice numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and
particularly not for consumption by accountants and auditors. Invoice
sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such
people get very nervous.

>but if ID say is 100, I cannot work out how to create
>
>renewal_invoice_100

Don't store the fixed text in your table at all. Instead use a Format property
of

"renewal_invoice_#"

to *display* the text.

             John W. Vinson [MVP]
0
John
12/1/2007 5:20:27 PM
On Sat, 1 Dec 2007 05:55:26 -0800, "Jeff Boyce"
<JeffBoyce_IF@msn.com-DISCARD_HYPHEN_TO_END> wrote:

>Geoff
>
>We aren't there.  We can't see what you're looking at.
>
>Where did "renewal_invoice_100" come from and what does it mean?

Jeff,

This was just an example of a more meaningful  invoice name, rather
than having it named simply by the ID value, i.e. renewal_invoice_100
rather than just 100.

>Please post the SQL statement of the query you are trying to use.

I haven't been able to get beyond having the ID value as the name so
no sql worth showing.

Cheers

Geoff

PS John not very impressed by the idea of using an autonumber ID
anyway!


0
Geoff
12/1/2007 10:41:29 PM
On Sat, 01 Dec 2007 10:20:27 -0700, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

>On Sat, 01 Dec 2007 11:46:16 +0000, Geoff Cox <<>> wrote:
>
>>Hello,
>>
>>I can create an invoice_number field in a query using the primary
>>field ID from the main table as
>>
>>invoice_number: ID
>
>If the primary key is an Autonumber, do be aware that there will be gaps in
>the numbering - any invoice that's deleted will leave a gap; even hitting
><Esc> after starting an invoice will leave  a gap; adding invoices using an
>Append query may leave a gap, often a huge one; replicating the database will
>make your invoice numbers random.
>
>In short... it's best NOT to use Autonumbers for human consumption, and
>particularly not for consumption by accountants and auditors. Invoice
>sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such
>people get very nervous.
>
>>but if ID say is 100, I cannot work out how to create
>>
>>renewal_invoice_100
>
>Don't store the fixed text in your table at all. Instead use a Format property
>of
>
>"renewal_invoice_#"
>
>to *display* the text.
>
>             John W. Vinson [MVP]

John,

I see what you mean re the use of the autonumber ID!

I'm not clear how to use renewal_invoice_#?

Incidentally apart from the autonumber ID value the only other unique
field value is the post code - this would look rather odd as an
invoice number and would not give a series of consecutive values - any
suggestions?

Cheers

Geoff
0
Geoff
12/1/2007 10:45:09 PM
On Sat, 01 Dec 2007 22:45:09 +0000, Geoff Cox <<>> wrote:

>I see what you mean re the use of the autonumber ID!
>
>I'm not clear how to use renewal_invoice_#?

Just what I say. There is NO need to store 16 identical text characters
renewal_invoice_ in every InvoiceID field in every record in your table. Use a
Long Integer number and use a Format property to *display* the text (without
storing it); the Format property

"renewal_invoice_#"

will do just that - display the text followed by a number.

>Incidentally apart from the autonumber ID value the only other unique
>field value is the post code - this would look rather odd as an
>invoice number and would not give a series of consecutive values - any
>suggestions?

Use a Long Integer number field instead of an Autonumber; and use VBA code on
the Form which (I hope!!) you're using to enter the invoices. Depending on how
many people are entering data concurrently, this can be very simple or fairly
complex. The simple end would work for one user (or for very low probability
of two users entering new invoices simultaneously); put code in the Form's
BeforeInsert event like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!InvoiceID = DMax("[InvoiceID]", "[Invoices]") + 1
End Sub


             John W. Vinson [MVP]

0
John
12/2/2007 1:40:25 AM
On Sat, 01 Dec 2007 18:40:25 -0700, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

>On Sat, 01 Dec 2007 22:45:09 +0000, Geoff Cox <<>> wrote:
>
>>I see what you mean re the use of the autonumber ID!
>>
>>I'm not clear how to use renewal_invoice_#?
>
>Just what I say. There is NO need to store 16 identical text characters
>renewal_invoice_ in every InvoiceID field in every record in your table. Use a
>Long Integer number and use a Format property to *display* the text (without
>storing it); the Format property
>
>"renewal_invoice_#"
>
>will do just that - display the text followed by a number.
>
>>Incidentally apart from the autonumber ID value the only other unique
>>field value is the post code - this would look rather odd as an
>>invoice number and would not give a series of consecutive values - any
>>suggestions?
>
>Use a Long Integer number field instead of an Autonumber; and use VBA code on
>the Form which (I hope!!) you're using to enter the invoices. Depending on how
>many people are entering data concurrently, this can be very simple or fairly
>complex. The simple end would work for one user (or for very low probability
>of two users entering new invoices simultaneously); put code in the Form's
>BeforeInsert event like
>
>Private Sub Form_BeforeInsert(Cancel as Integer)
>Me!InvoiceID = DMax("[InvoiceID]", "[Invoices]") + 1
>End Sub
>
>
>             John W. Vinson [MVP]

Thanks again John - you are a little ahead of me with some of the
above!

Cheers

Geoff
0
Geoff
12/2/2007 10:07:22 AM
I've found Autonumbers to be generally unfit for human consumption.  They
work for what they were designed to do, provide a unique row identifier.

-- 
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

"Geoff Cox >" <<> wrote in message
news:6ho3l3d7fjet405vggcv3j3tngu6orj4sh@4ax.com...
> On Sat, 1 Dec 2007 05:55:26 -0800, "Jeff Boyce"
> <JeffBoyce_IF@msn.com-DISCARD_HYPHEN_TO_END> wrote:
>
> >Geoff
> >
> >We aren't there.  We can't see what you're looking at.
> >
> >Where did "renewal_invoice_100" come from and what does it mean?
>
> Jeff,
>
> This was just an example of a more meaningful  invoice name, rather
> than having it named simply by the ID value, i.e. renewal_invoice_100
> rather than just 100.
>
> >Please post the SQL statement of the query you are trying to use.
>
> I haven't been able to get beyond having the ID value as the name so
> no sql worth showing.
>
> Cheers
>
> Geoff
>
> PS John not very impressed by the idea of using an autonumber ID
> anyway!
>
>

0
Jeff
12/2/2007 1:27:44 PM
Reply:

Similar Artilces: