Auto number, year, month number combination

[nrgbmwx5]

I need to create an auto number for service calls that show the following:

year as 08, 09 etc, month as a letter, Jan = A, Feb = B etc then an incremental number starting at 300 for each month/year combination.

So for example: 08A300

Can anyone help me as I am stuck?

 

[raskew]

...an incremental number starting at 300 for each month/year combination

As you've described it, there are a total of 12 month/year combinations (300 - 311) for each year. What do you intend to do with this piece of data?

Bob

 

[nrgbmwx5]

The reasoning behind this is that the call number shows at a glance what year, month it was generated and a unique number for the call out so for example:

08A300 would have been raised in 2008, in January and it's the first call number of 300, this number would then increase as more calls are received for January 2008.

Then if Feb the call number would be 08B300, call raised in 2008, in Feb and it's the first call number of 300.

Does this make sense?

 

[namliam]

Well it makes sence... tho using the A B C to tell the months doesnt... Course it is possible, tho illogical.

Would you want to go:
08A001
08A002
08A003
08A...
08A312
08A313
08A314
08B315
08B316
08B317
08B...

Or...
08A001
08A002
08A003
08A...
08A312
08A313
08A314
08B001
08B002
08B003
08B...

I think there is even a sample on this forum, tho I am currently unable to find it.

 

[nrgbmwx5]

The second example but starting from 300:

For Jan:

08A300
08A301
08A302

etc, etc.

For Feb

08B300
08B301
08B302

etc, etc.

For Mar

08C300
08C301
08C302

etc, etc,


Then for next year start at:

For Jan 09

09A300
09A301
09A302

etc, etc.

Thanks in advance for your help.

 

[namliam]

Using below code you can generate your key value.

Public Function MakeKey() As String
    Dim rs As DAO.Recordset
    Dim myKey As Variant
    Dim defaultStart As Integer
    Dim cMonth As String ' current month
    defaultStart = 300
    cMonth = Right(Year(Date), 2) & Chr(64 + Month(Date))
    Set rs = CurrentDb.OpenRecordset("Select max(KeyField) as MaxKey from yourTable")
    If rs.EOF Then
        MakeKey = cMonth & 300
    Else
        myKey = rs!MaxKey
        If Left(myKey, 3) = cMonth Then
            MakeKey = cMonth & Mid(myKey, 4) + 1
        Else
            MakeKey = cMonth & 300
        End If
    End If
End Function

But you have to assign the key value to the table via code via a form or something.

 

[nrgbmwx5]

Thank you for your respone, I'll let you know how it goes.

 

[nrgbmwx5]

Thank you for your help, however, once again I have lost the plot a tad, could/would you elaborate when you say "assign the key value to the table via code via a form or something"

I am creating a new table called call_numbers with the field name being call_number.

Thanks in advance.

 

[namliam]

When I say that I mean you cannot assign the key value in your table... You have to make a form or presumably you allready have one where you assign this function as the default value for your key field.
Then you can hide it so users cannot touch it (or whatever)

 

[Daz]

Thread helped me aloso

 

[jai kushwaha]

hello my dear friend I have a database every year the no start from current year/0001. like for this year it started from 2015/0001. now I want that every year on 1st April it start like this. I mean in the place of 1st January the autonumber reset on 1st april.... how to do it please help. I need it really as quick as possible thanks in advance...
help...