increment record number with year

[foxtet]

how do I increment record id like given below
2014/1
2014/2
2014/3
and when year ends
2015/1
2015/2

foxtet

 

[CJ_London]

the simple answer is use

Year(Date())

but you have not provided sufficient context so I suspect this is not what you want

 

[Steve R.]

The code below increments the project number by year. When a new year starts, it begins with "1". You will need to adapt the code below to suite your needs.

As a word of advice. Do not use the field which contains your "record number". The record number should be auto-generated by MS Access as a primary key. In my case, I used a field titled: "ProjectNum". Consider having a similar field.

Public Function NewProjectnum() As Long
    Dim lonCurrentYear As Long
    Dim lonLastnum As Long
    Dim lonTestYear As Long
    Dim intCaseSelected As Integer
    intCaseSelected = 1
    lonCurrentYear = Year(Date)
    lonLastnum = DMax("Projectnum", "ConsistencyMain")
    lonTestYear = Val(Left(lonLastnum, 4))
    If Val(Right(lonLastnum, 4)) >= 997 Then intCaseSelected = 2 'Rem Test for too many project numbers
    Select Case intCaseSelected
        Case 1 'Results OK
            If lonTestYear = lonCurrentYear Then NewProjectnum = lonLastnum + 1 'Rem increment the project number for current year
            If lonTestYear <> lonCurrentYear Then NewProjectnum = Val(Str(lonCurrentYear) + "0001") 'Rem increment the project number for the start of a new year
        Case 2 'Too many projects
            NewProjectnum = 9999
        End Select
End Function

 

[foxtet]

The project number need to be increment with year as given below
1/2014
2/2014
3/2104...... and when next year starts number can be reset and continue like

1/2015
2/2015
3/2015 so on

I have added the function but dont know how to apply that function to achive sequence

foxtet

 

[RainLover]

This attached Demo database may be of help.

It can be used in a variety of ways.

Sing out if you have a problem.

 

[Steve R.]

You will need to adapt the code below to suite your needs.

The forum, in part, is a learning experience. As I indicated, you will need to adapt your code. Specifically one difference between my code and your request is that my code is the format 2014XXXX, whereas your format is X2014. It is up to you to make those revisions. The code sample provided will serve as guidance for how to achieve the solution you requested.

As a hint, the code sample is triggered when a command button is pressed on a form to add a new project. The function does not execute by itself.

 

[foxtet]

can the output be format 2014/1, 2014/2..etc

 

[Steve R.]

can the output be format 2014/1, 2014/2..etc

Yes. Again you need to research the specifics. The structure is shown below. Do not simply copy it and expect it to work, there is a lot to be done before you get to the snip-it below.

strNewProjectnum = str(lonYear) & "/" &  str(lonSequenceNum)

 

[foxtet]

Thank you Steve for the technical explanation.

Could you work on the attached database to show how it works. Since I am very new to access i find it difficult to apply your code in practice..

Thank you for your time.

Fox

 

[Steve R.]

Could you work on the attached database to show how it works. Since I am very new to access i find it difficult to apply your code in practice.

My apologies but it is up to you to learn.

 

[RainLover]

Thank you Steve for the technical explanation.

Could you work on the attached database to show how it works. Since I am very new to access i find it difficult to apply your code in practice..

Thank you for your time.

Fox

Here is a start.

Do you have a written plan. I would want to see a plan that has each and every part detailed.

If you were to list them you may come up with more steps than you would have first required.

Perhaps you could start with the way the result will be stored and displayed.

Some small parts can be quite complicated so you need to break it down. The sample I gave you is as good as you will get as far as a completed project is concerned. I would have thought you would have asked for some explanations on the code.

Instead you have asked for someone to do it for you. Suggest you start again and put the hard yards in. It really is the way to learn.