Help updating an incrementing field

[Chuck M]

Hi - I have a system that generates invoices. I need to create an invoice #
field in each record that I can populate with the previously used invoice # +
1. Not sure if I can do this through SQL when the invoice records are
created or if I need to do it through VBA after the records have been
created. Any help would be greately appreciated.

 

[Lord Kelvan]

what is the format you want your numbers to be in?

 

[Chuck M]

Thanks for the quick reply. The format is the constant "EP" plus 5 numbers
zero filled. Ex. EP0001, EP0002 etc.

 

[Lord Kelvan]

ok there is the easy way and the hard way the easy way is really easy

go into your table and on invoice number make it an autonumber
datatype then down the bottom of the window you will see two tables
general and lookup

on the general tab you will see several values one of them is format
click on the corrisponding box and type "EP"00000 and then low and
behold it will do it for you so in any insert statement since it is an
autonum dont put it in the statement

you may have had

insert into table (invoicenum,field2,field3) ...

now make it

insert into table (field2,field3) ...

the hard way is a complex insert into statement

INSERT INTO table (invoicenum,field2,field3)
SELECT IIf(Max(CInt(Right(id,5)))<9,"EP0000" & Max(CInt(Right(id,1)))
+1,
IIf(Max(CInt(Right(id,5)))<99,"EP000" & Max(CInt(Right(id,2)))+1,
IIf(Max(CInt(Right(id,5)))<999,"EP00" & Max(CInt(Right(id,3)))+1,
IIf(Max(CInt(Right(id,5)))<9999,"EP0" & Max(CInt(Right(id,4)))+1,
"EP" & Max(CInt(Right(id,5)))+1)))) AS Expr1,"value2", "value3"

so yea do it the easy way

hope this helps
FROM table;

 

[Chuck M]

Thanks for the two methods. I already have an autonumber in the table for the
primary key and it's not letting me add a second autonumber. So I guess the
decision has been made for me which method to use.

Thanks again for the response!

 

[Chuck M]

Please ignore my previous reply. I'l just make the invoice # the primary key
and remove the other autonumber. Thanks!!!!