[SOLVED] Ms Access series numbering that resets monthly and yearly

Ms Access series numbering that resets monthly and yearly

I ran into a problem with my code.

My MS Access Database needs to reset the series number field at the beginning of a new month or year.

However when testing the database it works well until i get to the 10th record and afterwards i receive a duplicate value warning.

I'm totally confused on where i went wrong.

Please help? Code is pasted below:

Private sub form_beforeinsert(Cancel as integer)

dim vlast as variant
dim invnext as integer

me.invyear = format(date,"yyyy") & format(date, "mm")
vlast = dmax("SeriesNumber", "invoice", "InvYear='" & Me.invyear.value & "'")

if isnull(vlast) then
    invnext = 1
else
    invnext = vlast + 1
end if

me.seriesnumber = invnext
me.invoicenumber = format(date, "yyyy") & "-" & Format(date, "mm") & "-" Me.SeriesNumber

End Sub

Solution

It is because you seem to store everything as text. So, convert to a number to retrieve the numerical maximum value:

    vlast = DMax("Val([SeriesNumber])", "invoice", "InvYear='" & Me!invyear.Value & "'")