last invoice number

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi

Im trying to write a query that will come back with the last invoice number
used. From there I can add 1 to it to give it the next number on the form.

This field would be overwritable, how can i write a query that will check
that the number that is typed in does not already exist in the table?

Thanks
 
Dave

Your description seems somewhat conflicted. I am having trouble imagining a
situation in which I'd want a user to automatically receive the "next"
invoice number, but also have the ability to modify it.

Are you sure you want to be able to do both?

To find the last/next number in a sequence number field, take a look at the
DMax() function or a Totals query that returns the Maximum. Add 1.

Or is your "invoice number" non-numeric?
 
I know it sounds a bit conflicted but its all to do with giving the user
flexibility. The idea is they will be recording rahter than raising the
invoice so they could be entered in order and hence it would suggest the
next number however they may not be in order or there may be one missing so
it needs the flexibilty!

I think i know what you are getting at with the max function. How could I
check if the number has been used before and also how can i get the number
return from the max and drop it into a variable to add 1 to?

Thanks
 
To add 1 to your max:
dim NextInvoiceNum as number
NextInvoiceNum = dmax(ÃnvoiceNum",ÃnvoiceTable")+1

To find out if you used the number before, two ways
1.
if isnull(dlookup(ïnvoiceNum",ÃnvoiceTable",InvoiceNum="& InvoiceRequire) then
never used
else
been used
end if

2.
open recordset, tell me if there is a point to continue
 
Back
Top