sequencial #'s (Invoice or Check #'s) in Query

[Hal]

I am trying to create sequencial numbers (such as invoice or check #'s) in a
query?
I try to start with a known number stored in a table file.

 

[Lord Kelvan]

well there is the auto number functionality but that starts at 1 you
can do something like

insert into tblinvoice (invoicenumber,field2,field3)
select max(tblinvoice.invoicenumber) + 1,"value2", "value3"
from tblinvoice

just create the first record manually witht he first number in the
sequence

note that wont work with a text identifier

ie IV0001

nore will it allow leading 0's

if you want leading 0's you will have to make it a text field and then
cast it as an number

INSERT INTO tblinvoice(inviocenumber,field2,field3)
SELECT IIf(Max(CInt(inviocenumber))<9,"000" & Max(CInt(inviocenumber))
+1,IIf(Max(CInt(inviocenumber))<99,"00" & Max(CInt(inviocenumber))
+1,IIf(Max(CInt(inviocenumber))<999,"0" & Max(CInt(inviocenumber))
+1,Max(CInt(inviocenumber))+1))) AS Expr1,"value2","value3"
FROM mission;

and make field2 and field 3 and any other fields in yoru table the
field names and the value2 and value3 the valeus you want

hope this helps

regards
kelvan

from tblinvoice

 

[Lord Kelvan]

woops that from tblinvoice shoudl be where that from mission line in
the second query