Year and Autonumber

[cranebill]

Hello once again, I am stumped. I am creating a table in which work orders are genertaed from. To keep this table organised in the future i wanted to have the last 2 digits of the year in conjunction with the AutoNumber feature. I have tried a few things such as I made a field there the default value was year((now),"yy&quot) which gave me the last 2 digits, well minus the 0 which is another problem, and then also created an Autonumber field, thinking i could make an additional field's default value the combination of the 2 previous fields with the & command. didnt work of course. Is there a way to either join these 2 somehow or maybe create a manual autonumber field where the default value is the year and then the last number inputted +1.

 

[JeremyNYC]

You can certainly create a manual autonumber. There are some faqs on one of the access forums here telling you how to do that. MichaelRed has a method that works. If you need your Key to indicate how many whatevers there have been in a year, definitely use one of those solutions. Autonumbers are unique, but they cannot be relied upon to be sequential.

On the other hand, you can also just use
format(Date(),"yy&quot & [YourIDField] to display a zero-led year and then the autonumber field on a form or a report.

But it sounds likely that you'll want to generate your own sequential numbers.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[KenReay]

Hi

From an earlier post today, someone wanting to do something pretty similar:

Something like:

Public Function NextNo(strPrefix As String) As Long
Dim Db As Database
Dim Rs As Recordset
'
Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("SELECT lngAuto FROM MyTable WHERE strPrefix ='" & strPrefix & "' ORDER BY lngAuto DESC;&quot
If Rs.RecordCount = 0 Then
NextNo = 1
Else
NextNo = Rs!lngAuto + 1
End If
Rs.Close
Set Rs = Nothing
Set Db = Nothing

End Function

Notes

The above is not tested

If you are using ADO, rather than DAO, you will have to substitute appropriate ADO commands

You need to put in error trapping, particulalarly if your application is multiuser, to trap possibility of two users asking for next number at same time, and getting a duplicate Number

You need to substitute your own table and column names

If you vall above so:

strWONumber = NextNumber(Right(Year(Format(Date()),"0000&quot,2))
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[cmmrfrds]

It seems you are making it harder than need be. Just put a field in your table to capture the fiscal/calendar year - thats all you need. As long as both autonumber and date are in the record you can combine as needed at any point.

 

[cranebill]

Sorry for the delay in a response but i got a little side tracked. I do thank you guys for all the ideas and once i get it worked out i will post which option i chose to maybe help others in the future.