Auto number... well not quite

  • Thread starter Thread starter Shane
  • Start date Start date
S

Shane

I'm setting up a sales form where the sales number has the format
YYYY####### . YYYY is the year, example 2004 and ####### starts at
0000001 and goes up by one for each new sale during the year. At the
end of the year the year moves up and the #### starts back at 1 again.
I looked at autonumber but that starts at 1 and continues to go up even
after the year switches. I haven't been able to find a good way to
automatically update the SaleNO field, any ideas?

Thanks
Shane
 
How about:

Public Function DateIncrement(strField As String, strTable As String) As
String
'********************************************************************
' Name: DateIncrement
'
' Inputs: strField As String
' strTable As String
'
' Returns: String
'
' Author: Arvin Meyer
' Date: February 05, 2004
' Comment: Increments last 2 digits based on date (Year and DayNumber)
' Format is yyyyddd-00
'
'********************************************************************
On Error GoTo Err_DateIncrement
Static intNum As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Select " & strField & " From " & strTable & "
Order by " & strField & ";")

If Not rst.EOF Then
rst.MoveLast
If Left(rst.Fields(strField), 7) = Year(Date) & Format(DatePart("y",
Date), "000") Then
intNum = Val(Mid(rst.Fields(strField), 9)) + 1
Else
intNum = 1
End If
Else
intNum = 1
End If

DateIncrement = Year(Date) & Format(DatePart("y", Date), "000") & "-" &
Format(intNum, "00")

Exit_DateIncrement:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

Err_DateIncrement:
MsgBox Err.Description
Resume Exit_DateIncrement

End Function

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Thanks gents. I tried every way I could to use MAX(field) + 1 and DMAX
in the default value but creating the function and setting the default
value equal to the function worked like a charm.
 
Back
Top