create a field in a table

[rainbows]

hi , is it possible to create a field in a table which would give the the words " PC0 " and the auto number like PC05, then PCO7 ETC . see the field "batch number " this is done manually but i want to make it automatic this in for every line item and i know you cannot have 2 auto numbers in one table

thanks steve

 

[plog]

Yes, but why?

Why not just prefix 'PC' onto the record's autonumber? Another method is to create a numeric field in your table, use DMAX (https://www.techonthenet.com/access/functions/domain/dmax.php) to retrieve the highest number and then add one to it for the next record.

 

[GPGeorge]

hi , is it possible to create a field in a table which would give the the words " PC0 " and the auto number like PC05, then PCO7 ETC . see the field "batch number " this is done manually but i want to make it automatic this in for every line item and i know you cannot have 2 auto numbers in one table

thanks steve


View attachment 102423

When creating values for human consumption, AutoNumbers are not a good choice anyway. They are guaranteed only to be unique within that table. There is no assurance AutoNumbers will be sequential, which makes them unsuitable for tasks like this BatchNo. You could easily end up with missing batch numbers if you were to mistakenly use an AutoNumber in that field.

Use one of the many code examples to generate an increasing, sequential value for the BatchNo, such as the one Plog linked to. A BinGoogle search will, no doubt, locate others.

 

[rainbows]

Why not just prefix 'PC' onto the record's autonumber?


could you please advise how to do that

thanks steve

 

[plog]

Data storage and data display are two different things. If you always want 'PC' to appear before a field you can make a query and use this field:

PCID: 'PC' & [YourIDField]

Then when you need the PCID in a form or report you can reference that query and field. You can also directly do it in the form by setting the inputs source to the same thing. That way there's no need to touch the table, you simple control how it gets displayed.

 

[rainbows]

i will be using it to create purchase orders also and i need to quote that number and that relates to the product so it possible i would like it to go into the field called batch Number

 

[GPGeorge]

i will be using it to create purchase orders also and i need to quote that number and that relates to the product so it possible i would like it to go into the field called batch Number

It makes little difference WHERE it is stored, as the Primary Key for the table, or in a redundant, "BatchNumber" field. Either can be included in the PO by adding it to the query which returns the records for the PO. Adding any sort of adornment, i.e. the human useful prefix, "PC0", does not change the underlying value and is purely "human eye friendly".

The reason I suggested a separate BatchNumber is that option allows you to avoid the problem of missing values that inevitably occur in AutoNumber fields. If you can account for all of your batches without worrying about that problem of gaps in the Batch Number, then there's no additional value added by that separate field.

 

[Galaxiom]

Data storage and data display are two different things. If you always want 'PC' to appear before a field you can make a query and use this field:

PCID: 'PC' & [YourIDField]

Or alternatively put this expression in the Format Property of the form or report control (or field in a table):
"PCO"#

The control will remain bound and data can still be entered if desired. The PCO will disappear when the control is in focus.

 

[Pat Hartman]

Here's a sample database that shows how to create two different types of sequence numbers. It might help you to create a custom sequence number.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk