Auto Number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to find out how to get this specific auto number format (in a table)-

4035-0

The 4 represents the year. 200
The 035 represents day of the year. February 4th
-01 would represent each input for that specific day

This would need to start 4036-01 for February 5th and so on and so on
 
Charles,

To begin with, let's get things straight: the autonumber field type is one
with its own personality, which you cannot do much on (other than just
select a display format, or thick it to a specific starting value).
Basically, it will just be incrementing or random integer values (long
integer data type), and there's no way to change this.

What you are after is not an autonumber, it is actually not even a number!
It's a structured text field. In order to get it to auto-calculate, you need
to employ a data entry form for manual entry, or an append macro for
uploaded data, in which the field value is calculated by a (scary?)
expression like:

=Right(Year(Date()),1) & Format(Date()-DateSerial(Year(Date()),1,1)+1,"000")
& "-" &
Format(Val(Right(Nz(DMax("[FieldName]","TableName","Left([FieldName],4)=Righ
t(Year(Date()),1) &
Format(Date()-DateSerial(Year(Date()),1,1)+1,"000")"),"00"),2)+1,"00")

Where you neet to change FieldName and TableName to the actual ones.

Better yet, use a function in code to calculate it:

Function Get_Key()

vYear = Right(Year(Date()),1)

vDay = Format(Date()-DateSerial(Year(Date()),1,1)+1,"000")

vLast = Val(Right(DMax("[FieldName]","TableName","Left([FieldName],4)= '
" & vYear & vDay & " ' "),2))

Get_Key = vYear & vDay & "-" & Format(Nz(vLast,0),"00")

End Function



HTH,

Nikos
 
What happens in 2010? You not only have a Y2K problem, you want to be beat
up in 6 years. I've changed you request to a format of 2004036-01. Feed this
your field name and table name in quoted and you'll get what you need. Put
the code in a standard module. Set the textbox's DefaultValue to: -
DateIncrement("YourFieldName", "YourTableName")


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 2004036-01
'
'********************************************************************
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
 
Back
Top