Sequential numbering reset at new month

[PhilUp]

I am looking to have a sequential number generated automatically that should look as follow

YYMM+sequence

Each new month, the sequence part would restart from one

Example for June:

160601
160602
160603
...

For July as of July 1st:
160701 etc....

What the easiest solution to achieve that ?

 

[Ranman256]

in the form at save time, get the new Seq# from a query that pulls all records for the month:

iCount = Dcount("*","qsQryRecsForThisMonth")
NewNum = format(txtDate,"yymm") & iCount +1

 

[arnelgp]

create a table with one field (string, let say your fieldname is Sequence) that will serve for your sequence.
create a function that will do the sequence for you.

public function fnSequence() as variant
fnSequence = DMax("Sequence", "yourTable", "Left([Sequence],2) = Format((Date(),'yy') And Mid([Sequence], 3, 2)= Format(Date(), 'mm')")
if IsNull(fnSequence) Then
fnSequence = Format(Date(),"yy") & Format(Date(),"mm") & "01"
else
fnSequence = Format(Val(fnSequence) + 1, "000000")
end if
end function

you need a form to achieve this.
either call the function on before insert event of your form to insert the sequence to your textbox field.

 

[plog]

First, nothing in the world is "automatic"--an action will need to trigger this. Most likely a form event that fires when a user creates a new record.

Second, you will need to set up your table to accomodate this, its not just going to be 1 field, it will be require 2: RecordDate and RecordNumber. RecordDate will be a date/time field and hold when the record is created and will be where you get the YYMM value. RecordNumber will be a numeric field and will hold the sequential part.

When an action is taken to create a record (possibly a submit button, or an action on a particular input) you will fire off a custom function, let's call it 'get_SequenceNumber', which will return the next RecordNumber to use.

get_SequenceNumber will use a DMAX(http://www.techonthenet.com/access/functions/domain/dmax.php) to get the last used number add 1 to it and return that valule to use. If no number has been used for the month it uses 1.

That's the storage part, the display part will require you to extract the Month and Year from the RecordDate, format it with leading 0's and then concatenate the RecordNumber to it with leading 0's as well.

 

[PhilUp]

Thank you Arnelgp,

I have made one table "QuoteNumbers"with [QuoteID, and [Sequence] field names.
I have made one form based on "QuoteNUmbers" table with the two textboxes.

I have add a button to call the function but I get some error that say:
Compile Error: Expected End Sub.

Here is the code:

Option Compare Database
___________________________________________________________
Private Sub Command2_Click()
Public Function fnSequence() As Variant
fnSequence = DMax("Sequence", "QuoteNumbers", "Left([Sequence],2) = Format((Date(),'yy') And Mid([Sequence], 3, 2)= Format(Date(), 'mm')")
If IsNull(fnSequence) Then
fnSequence = Format(Date, "yy") & Format(Date, "mm") & "01"
Else
fnSequence = Format(Val(fnSequence) + 1, "000000")
End If
End Function
End Sub

 

[PhilUp]

Thank you Plog for getting to the exactness of the wording.
I can do some limited code but not to that extend for now.

And actually I don't really want to "fire off" a function but rather calling it.

 

[arnelgp]

actually you have to paste the function in a Module.

Public Function fnSequence() As Variant
Dim ret As Variant
ret = DMax("Sequence", "QuoteNumbers", "Left([Sequence],2) = Format((Date(),'yy') And Mid([Sequence], 3, 2)= Format(Date(), 'mm')")
If IsNull(ret) Then
ret = Format(Date, "yy") & Format(Date, "mm") & "01"
Else
ret = Format(Val(fnSequence) + 1, "000000")
End If
' add this number to our table
Docmd.SetWarnings False
Docmd.RunSQL "Insert Into QuoteNumbers (Sequence) SELECT " & ret & ";"
fnSequence = ret
End Function

 

[PhilUp]

Thanks Arnelgp,

Will try tomorrow and will let you know. Thanks again for your help.

 

[PhilUp]

I have tried but not sure how to do exactly to make it work.

After I create a new module and paste the code, how to call it up from my form ?

Cannot use a button ?

 

[arnelgp]

on the click event of your button, set the value of the textbox that will receive the sequence number:

private sub yourCommandButtonName_Click()
Me.textBoxToReceivedSequence = fnSequence()
end sub

 

[PhilUp]

Thank a lot for your help.
I did as you said but I am getting the following error:

Missing ),] or Item in query expression 'Left([Sequence],2) = Format((Date(),'yy') And Mid([Sequence], 3, 2)= Format(Date(), 'mm')'.

 

[arnelgp]

sorry about that, i have to test it this time.

Public Function fnSequence() As Variant
Dim ret As Variant
ret = DMax("Sequence", "QuoteNumbers", "Left([Sequence],2) = " & Format(Date, "yy") & " And Mid([Sequence], 3, 2) = " & Format(Date, "mm"))
If IsNull(ret) Then
ret = Format(Date, "yy") & Format(Date, "mm") & "01"
Else
ret = Format(Val(ret) + 1, "000000")
End If
' add this number to our table
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert Into QuoteNumbers (Sequence) SELECT " & ret & ";"
fnSequence = ret
End Function

 

[plog]

I highly recommend not cramming discrete pieces of data into one field.

Are you ever going to want to run a report by Year/Month? Or even just Year? If so, this method is going to be a pain to work with. Forget about trying to run a Year to date report.

Discrete pieces of data need to be stored discretely (in their own field).

 

[PhilUp]

Great ! Thank you so much, works perfectly.

 

[PhilUp]

Plog, I see no issue in filtering them later to make reports, easy to select only the year or the month part. Just by looking at the number we know how many quotes have been made each month, and the year they were made.

Anyway, thanks for your input.