hi...

I have a table which contains ReceiptNo(Number),ICNo(Number), StickerNo(Autonumber),PlateNo(text) and Date.

Can anyone please tell me how to generate an autonumber back to 1 for each beginning of the month.

Actually, you would never do that. You see, an autonumber's purpose is simply to identify the record and match it up with other records pertaining to it contained in other tables. It would be the key field in the table.

If you have a field that needs to be reset to 1 each month, then this field would not be an autonumber field. It would be simply a case number or transaction number, etc. You would increment this number by one for each added record, but the other field with the autonumber would just keep going higher and higher.

The case number or transaction number (the one that's reset to 1 each month) would be visible to the user, but the autonumber (that's never reset) is never seen by anyone. Again, it's simply used to tie data from one record in one table to another record (or records) in another table.

Example Table: The autonumber is simply used to match Record 7, for example, to Record 7 in another table. This number will never be seen by anyone. The computer uses it, not the user. The only number you care about the one you reset every month (transaction number).

That's why Microsoft Access made the autonumber "un-resetable."



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Greg is 100% correct in stating that autonumber fields (and indeed record numbering) in Access is just to identify a record. But there are ways to reset the autonumber field -- I just wouldn't recommend it...

Anyway look at:

http://www.mvps.org/access/general/gen0006.htm for reset info
http://www.mvps.org/access/general/gen0025.htm for autonumber usage
http://www.mvps.org/access/tables/tbl0005.htm to start autonumber from something other than 1
http://www.mvps.org/access/tables/tbl0016.htm for creating an autonumber field from code.

If you use the reset info above just delete the field and then use the last example to add the autonumber field via code to your table. You could even set this up in code with such simple logic as ...

Private Sub MyForm_Load

   Dim dtmDate as Date

   dtmDate = Date()

   FirstOfMonth(dtmDate)

End Sub

Function FirstOfMonth(dtmDate As Date)
' Return a date that is the first day of the month of the date passed
Dim D As Integer, M As Integer, Y As Integer

    If IsNull(dtmDate) Then
        FirstOfMonth = Null
    Else
        D = Day(dtmDate)
        M = Month(dtmDate)
        Y = Year(dtmDate)
        FirstOfMonth = DateSerial(Y, M, 1)
    End If
End Function

If the date equals the First day of the month then your logic can run the code to delete the autonumber field and then readd it to your table - now there is automation!!

Hope this helps you....

Kenny Alligood

Here's an alternate way of calculating the beginning and ending day of the month:

Public Function BegOfMonth(DateIn As Date) As Date
BegOfMonth = DateAdd("d", -(Day(DateIn) - 1), DateIn)
End Function
Public Function EndofMonth(DateIn As Date) As Date
EndofMonth = DateAdd("d", -1, (DateAdd("m", 1, BegOfMonth(DateIn))))
End Function

[email protected]

The beginning day of any month is simply 1, i.e. DateSerial(YYYY, M, 1). The end date is DateSerial(YYYY, M+1, 0), where YYYY is the year and M is the month of interest. So if you want to know the last day of February this year, it's DateSerial(2005, 3, 0) = February 28, 2005.



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division