Custom Autonumber (yearmonthcombination)

pathfinder225 · Jun 5, 2009

hi frnds,i am a newbie to access i have one field (AUTONUMBER) in my table i want it's format as "YYMMXXX" where, YY=last two digits of year ,MM=two digits of month

XXX=SEQUENCE TO be INCREMENTED.

values must be like this...suppose if date is 05-jun-09

0905000
0905001
0905002
.........
0912000

next year...2010
1001000
... how to achieve this.....by making this combination, i can use it for next 10 yrs ...

i have no idea of VBA i searched on many forums people suggesting some VBA code .....and format functions to achieve this .anyone plz explain me abt this in detail ....Anyhelp would be greatly appreciated!!

Atomic Shrimp · Jun 5, 2009

You can't do this with a true autonumber - all you can ever expect that to do is to be unique on each row.

You'll need to write a custom function for this - to look up the highest-numbered instance of the current month in the existing table, add one to the numeric value of the last three digits and push that into the new record.

But I can't help thinking this plan is a disaster waiting to happen...

-What happens if you have more than 1000 records in a month?
-What happens in ten years' time - will it just stop working, or do you plan to delete/archive the records from the previous cycle?
-What purpose is fulfilled by this scheme? Does it really have to be like that?

pathfinder225 · Jun 6, 2009

-What happens if you have more than 1000 records in a month?

Many Thanks for Replying...I'm 100% sure there won't be more than 400 records in a month....

-What happens in ten years' time - will it just stop working, or do you plan to delete/archive the records from the previous cycle?
-What purpose is fulfilled by this scheme? Does it really have to be like that?

Well this Autonumber is Unique Id (SERVICE NO)Given to Each Customer who Requests a Service ....there won't be more than 400 requests in a month....and generalizing it to atleast 5 years....can u suggest me any other alternative for this autonumber!!!

Galaxiom · Jun 6, 2009

I don't understand the problem with ten years. The scheme will work as is until 2099.
If more than 1000 records in a month just make the field eight characters long. If you get to servicing more than 10,000 cars a month you would probably have a different database anyway.

Doesn't need VBA just a huge expression in Field1 of your append query.

Right(Year(Date())+100,2) & Right(Month(Date())+100,2) & IIf(Left(Max([Field1]),2)=Right(Year(Date())+100,2) And Mid(Max([Field1]),3,2)=Right(Month(Date())+100,2),Right(Max([Field1])+1001,3),"000")

Note: The Right([field]+100,2) terms produce leading zeros.

pathfinder225 · Jun 6, 2009

Thank you mate for taking time to answering my question.

Doesn't need VBA just a huge expression in Field1 of your append query.

Right(Year(Date())+100,2) & Right(Month(Date())+100,2) & IIf(Left(Max([Field1]),2)=Right(Year(Date())+100,2) And Mid(Max([Field1]),3,2)=Right(Month(Date())+100,2),Right(Max([Field1])+1001,3),"000")

Note: The Right([field]+100,2) terms produce leading zeros.

well i don't know abt append query but managed and googled it and learned a bit about it that, it is used to select records from one or more data sources and copies the selected records to an existing table. Can u tell how this will help to get my Autonumber with format(YYMM000) what changes should i make in order to achieve this. Can i get this by editing in the format property of autonumber. Where to paste the code u gave me. here is my database schema...

Code:

service_no - AUTONUMBER
customer_name- Text
customer_addr- Text
service_info- Text
date- Date/Time

Best Regards,
Pathfinder

Atomic Shrimp · Jun 8, 2009

GalaxiomAtHome said:
I don't understand the problem with ten years. The scheme will work as is until 2099.

Ah. Right you are - not sure what I was thinking there.

If more than 1000 records in a month just make the field eight characters long. If you get to servicing more than 10,000 cars a month you would probably have a different database anyway.

Probably - I didn't know it was about servicing cars when I raised the point though.
I have seen (and worse, had to fix) the It'll Never Happen scenario quite a number of times in the past - so I've got a bit of an itchy trigger finger for it now.

Atomic Shrimp · Jun 8, 2009

pathfinder225 said:
Can i get this by editing in the format property of autonumber.

No, there's no way to implement this in the actual table design itself -pathfinder255 is suggesting you use an expression like that one in an append query to add new records with the appropriate code in them. (I couldn't get it to work that way, though).

Custom Autonumber (yearmonthcombination)

pathfinder225

New member

Atomic Shrimp

Humanoid lifeform

pathfinder225

New member

Galaxiom

Super Moderator

pathfinder225

New member

Atomic Shrimp

Humanoid lifeform

Atomic Shrimp

Humanoid lifeform

Similar threads

Users who are viewing this thread