Adding numbers automatically in a sequence

[oni1983]

I have a database which has a number of entries, each entry having a start date and end date. I would like to find a way to assign numbers 1,2 and 3 depending on the start dates of each entry. This needs to be done so that I can then choose all numbered 1 and group them together in a report.

Would it also be possible to automatically give new entries the next consecutive number whilst inputting?

 

[Stridhan]

Can you provide an example?

 

[tiedied61]

but add a GroupNumber field to your table and then create update queries (one for each grouping) similar to this one:

UPDATE MyTable SET MyTable.GroupNumber = 1 WHERE MyTable.StartDate Between #1/1/2016# And #1/10/2016#

UPDATE MyTable SET MyTable.GroupNumber = 2 WHERE MyTable.StartDate Between #1/11/2016# And #1/23/2016#

etc.

I am not quite sure if the above is what your were looking for


For consecutive numbering ... you can do this as an AfterInsert event of your form, run the code similar to the following

Private Sub Form_AfterInsert()

[INDENT]Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim NextNum as Long

Set db = CurrentDB()

' Get the highest stored number and add 1
Set rs = db.OpenRecordSet("SELECT Max([GroupNumber]) FROM MyTable")
NextNum = rs(0) + 1
rs.close
Set rs = Nothing

' Use the form's RecordsetClone so as not to disturb the user interface
Set rs = Me.RecordsetClone

' Move to the last record added
rs.Bookmark = Me.Recordset.LastModified

' Running SQL here instead of doing an Edit, allows other users to see the change instantly
' when running a shared program.  This way you don't accidentally have 2 users end up with
' the same NextNum due to network lag.

db.Excecute "UPDATE MyTable Set GroupNumber = " & NextNum & " WHERE [IDField] = " & rs("IDField"), 
dbFailOnError
Set db = Nothing[/INDENT]

End Sub

Hope this Helps

Art
MS Certified Access Developer
25+ years experience