If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Autonumbers with year included
Hi, ive been looking at the below code which i got from another discussion.
the code is designed to include the current year within the autonumber. The trouble is im not sure at all where to put this code into my access 2003 database. i have a table with a field that is autonumber and i have a form which uses that field to display the autonumber. where would the code go Function DateNum() As String '************************************************* ******************* ' Name: DateNum ' Purpose: Generate an incremental "number" based on the year ' ' Author: Arvin Meyer ' Date: July 27, 2003 ' Comment: Assumes Table1 As Table and CaseNum As Field ' Generates in the format of 03-0001, 03-0002, etc. ' Seed the first number if other than 0000 '************************************************* ******************* On Error GoTo Error_Handler Dim intNumber As Integer Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb Set rs = db.OpenRecordset("Select [CaseNum] from [Table1] order by [CaseNum];") If Not rs.EOF Then rs.MoveLast If Left(rs.Fields("CaseNum"), 4) = CStr(Year(Date)) Then intNumber = Val(Mid(rs.Fields("CaseNum"), 3)) + 1 Else intNumber = 1 End If End If DateNum = Format(Year(Date),"yy") & "-" & Format(intNumber, "0000") With rs .AddNew !CaseNum = DateNum .Update End With Exit_He rs.Close Set rs = Nothing Set db = Nothing Exit Function Error_Handler: 'If someone is editing this record trap the error Dim intRetry As Integer If Err = 3188 Then intRetry = intRetry + 1 If intRetry 100 Then Resume Else 'Time out retries MsgBox Err.Number, vbOKOnly, "Another user editing this number" Resume Exit_Here End If Else 'Handle other errors MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Problem Generating Number" Resume Exit_Here End If End Function -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads http://www.datastrat.com http://www.mvps.org/access |
#2
|
|||
|
|||
Autonumbers with year included
It may be a matter of semantics. In Access, an Autonumber is generated ONLY
by Access -- you have no control over it, and cannot "include" a year in it. I believe Arvin's code sample was intended to help you come up with a replacement for the Autonumber. Note: if you have both a year and a numbering scheme, you need two fields, not one. You can always concatenate the two values together in a query to display on a form, but you really don't want to store more than one (kind of) fact in one field. Regards Jeff Boyce Office/Access MVP "StuJol" wrote in message ... Hi, ive been looking at the below code which i got from another discussion. the code is designed to include the current year within the autonumber. The trouble is im not sure at all where to put this code into my access 2003 database. i have a table with a field that is autonumber and i have a form which uses that field to display the autonumber. where would the code go Function DateNum() As String '************************************************* ******************* ' Name: DateNum ' Purpose: Generate an incremental "number" based on the year ' ' Author: Arvin Meyer ' Date: July 27, 2003 ' Comment: Assumes Table1 As Table and CaseNum As Field ' Generates in the format of 03-0001, 03-0002, etc. ' Seed the first number if other than 0000 '************************************************* ******************* On Error GoTo Error_Handler Dim intNumber As Integer Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb Set rs = db.OpenRecordset("Select [CaseNum] from [Table1] order by [CaseNum];") If Not rs.EOF Then rs.MoveLast If Left(rs.Fields("CaseNum"), 4) = CStr(Year(Date)) Then intNumber = Val(Mid(rs.Fields("CaseNum"), 3)) + 1 Else intNumber = 1 End If End If DateNum = Format(Year(Date),"yy") & "-" & Format(intNumber, "0000") With rs .AddNew !CaseNum = DateNum .Update End With Exit_He rs.Close Set rs = Nothing Set db = Nothing Exit Function Error_Handler: 'If someone is editing this record trap the error Dim intRetry As Integer If Err = 3188 Then intRetry = intRetry + 1 If intRetry 100 Then Resume Else 'Time out retries MsgBox Err.Number, vbOKOnly, "Another user editing this number" Resume Exit_Here End If Else 'Handle other errors MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Problem Generating Number" Resume Exit_Here End If End Function -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads http://www.datastrat.com http://www.mvps.org/access |
#3
|
|||
|
|||
Autonumbers with year included
You should not be including the year in an autonumber field.
"StuJol" wrote: Hi, ive been looking at the below code which i got from another discussion. the code is designed to include the current year within the autonumber. The trouble is im not sure at all where to put this code into my access 2003 database. i have a table with a field that is autonumber and i have a form which uses that field to display the autonumber. where would the code go Function DateNum() As String '************************************************* ******************* ' Name: DateNum ' Purpose: Generate an incremental "number" based on the year ' ' Author: Arvin Meyer ' Date: July 27, 2003 ' Comment: Assumes Table1 As Table and CaseNum As Field ' Generates in the format of 03-0001, 03-0002, etc. ' Seed the first number if other than 0000 '************************************************* ******************* On Error GoTo Error_Handler Dim intNumber As Integer Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb Set rs = db.OpenRecordset("Select [CaseNum] from [Table1] order by [CaseNum];") If Not rs.EOF Then rs.MoveLast If Left(rs.Fields("CaseNum"), 4) = CStr(Year(Date)) Then intNumber = Val(Mid(rs.Fields("CaseNum"), 3)) + 1 Else intNumber = 1 End If End If DateNum = Format(Year(Date),"yy") & "-" & Format(intNumber, "0000") With rs .AddNew !CaseNum = DateNum .Update End With Exit_He rs.Close Set rs = Nothing Set db = Nothing Exit Function Error_Handler: 'If someone is editing this record trap the error Dim intRetry As Integer If Err = 3188 Then intRetry = intRetry + 1 If intRetry 100 Then Resume Else 'Time out retries MsgBox Err.Number, vbOKOnly, "Another user editing this number" Resume Exit_Here End If Else 'Handle other errors MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Problem Generating Number" Resume Exit_Here End If End Function -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads http://www.datastrat.com http://www.mvps.org/access |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mortgage formula | zyus | Setting Up & Running Reports | 3 | October 14th, 2005 08:04 AM |
year to year comparison | steverizzo | Running & Setting Up Queries | 1 | March 12th, 2005 06:03 PM |
Show last year first, but keep data sorted cronologically | Bob Richardson | General Discussion | 11 | July 22nd, 2004 10:00 PM |
IIF statement for Last Fiscal Year | Liz | New Users | 5 | May 18th, 2004 07:02 PM |
date conversion | web_time | Worksheet Functions | 10 | December 25th, 2003 06:51 PM |