AutoNumber

[connerlowen]

Hi,

I am creating a database for creating quotations. The quotation number is generated using the date, for example the first quote today would be quote number "05202015-1" because it is the first one today. The next quote today would be quote number "05202015-2" and so on. Is there a way to make access automatically generate these quote numbers based on the date? if so how can I do it, and if not what do you suggest?

Thanks.

 

[gemma-the-husky]

split the reference into 2

datepart and sequential no

then the sequential number is simply

seqnumber = nz(dmax("seqnumber","table","seqdate = " & checkdate"),0)+1

otherwise you have to get into complicated string slicing.

----
note that you are better reversing the date

yyyymmdd, as then the dates will be sorted naturally
20150520

if you sort forwards, you will find you have numbers starting 01, 02 etc ineach month. That will be a nightmare.

 

[connerlowen]

where would that code be?

 

[apr pillai]

Insert the expression within the Before_Insert() Event Procedure on the Data Entry Form. Give the target form control name at the left-side of the expression.

www.msaccesstips.com/2012/01/autonumber-with-date-and-sequence-number.html