autonumber field format

[amna77]

Hi on my form, I have one autonumber field. is there anyway that I format autonumber field, so it gives me YYYYMMDD-###, I want current date like year, month and day and then - ###, about ###, every day I want to start with 001. for exaple if they enter 3 record per day, then it should be YYYYMMDD-001, YYYYMMDD-002, YYYYMMDD-003. and then next day these ### going to start again 001, 002, etc.
Please help me with that. i shall be thankful to you.
Thanks in advance
Amna

 

[Ziggy]

Forget about formating your date field, just put a text box on the form with this as the control source:

=([DateField] & " - " & [IDField])

Ziggy

 

[amna77]

Thanks for help. But any idea how to increment number by date. If I enter 3 record per day, then its should be date-001, date-002, date-003. now next day I want to start 001 again, not from 004. Can you help me with that.
Thanks in advance
Amna

 

[dk]

Hi Amna,

There isn't a built in way of doing this - an autonumber field can only be a long integer number, nothing else. To do what you need will require some VBA in order to generate a primary key value for each new record in your database. The function below will create a value based on today's date e.g. start with 20030804-001, then 20030804-002 and so on. You will need to change the line that starts with strSQL=... so that it refers to your actual table and field name. This code assumes at least Access 2000.

Function GetNextPKValue() As String

    Dim adoRS As ADODB.Recordset, strSQL As String
    Dim strPotentialValue As String, lngSuffix As Long


    strPotentialValue = Format(Date, "YYYYMMDD")


    'Search to see if any record exists for the current day
    'CHANGE THIS TO MATCH YOUR TABLE/FIELD NAME
    strSQL = "SELECT TOP 1 MyTable.ID FROM MyTable WHERE MyTable.ID Like '" & _
             strPotentialValue & "%' ORDER BY MyTable.ID DESC;"

    Set adoRS = CreateObject("ADODB.RecordSet")

    adoRS.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly


    If adoRS.BOF And adoRS.EOF Then     'There are no values for this date
        GetNextPKValue = strPotentialValue & "-001"  'so it must be record number 1 for the current date
        adoRS.Close
        Exit Function
    End If


    GetNextPKValue = Left$(strPotentialValue, 8) & Format(Val(Right$(adoRS.Fields(0), 3)) + 1, "-000")
    adoRS.Close

End Function

Obviously, you'll now need to call the GetNextPKValue function any time you add a new record to the table in question, because it won't populate automatically like an autonumber field would. E.g. if you're using a form and want to add a new record try something like this:-

Private Sub NewRecord_Click()

    DoCmd.GoToRecord , , acNewRec
        
    'ID is a bound to our date/number field
    Me.ID = GetNextPKValue

End Sub

 

[amna77]

Thankyou very much Dan, you are the best. Its working now the way I wanted it. Thanks for great help.


Thanks
Amna