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

  • Thread starter Thread starter Hal
  • Start date Start date
H

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.
 
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
 
woops that from tblinvoice shoudl be where that from mission line in
the second query
 
Back
Top