adding name /creating field/query?

[Geoff Cox]

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

 

[Jeff Boyce]

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


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

 

[John W. Vinson]

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]

 

[Geoff Cox]

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!

 

[Geoff Cox]

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.


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

 

[John W. Vinson]

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]

 

[Geoff Cox]

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

 

[Jeff Boyce]

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


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