Question Custom Request Numbers

[abusaif]

Hi all,

I was wondering if could tell me about creating the custom Request number as follows:

Jan-001, Feb-001, and so on.

Explaination: My number contains first three Characters of the the current month and after hyphen requisition starts. To be noticed that we do create more than thousand requisitions in a month. And when the new month starts first three characters will change and the numbers will restart from 001.

 

[Beetle]

This sort of thing isn't usually all that difficult to do, but we need more info;

1) Is there a Date/Time field in the table from which the month can be derived? (I would think so, but you didn't say and I try not to assume)

2) How do you want to handle the same month from different years?

 

[abusaif]

Dear Beetle!
Thanks alot, and Yes I do have a date and time fields in my table.
Also your question is attractive. (Would it be possible to create an order number with all three ie. Year Month and Number, if your answer is yes then my format would be
Jan-0001-2012 )

 

[jdraw]

I agree with Sean that such a set up could be done. But I have to ask if there is a business need that requires this. I would recommend you still use an autonumber for PK that Access can use. If you want some sort of presentation for a Request number, then go for it.

However, I tend to like the one field one meaning approach.

 

[Beetle]

A sample file is attached. It has one table and one query.

Notes:

• The table has an Autonumber PK. As JDraw suggested you should not try to use your derived Request Number as any sort of key value in the table (in fact it shouldn't be stored at all)
  
• The table has a Date/Time field that stores both the date and the time of the creation of each record. The Request Number is derived completely from this Date/Time field. No part of the Request Number is stored in the table.
  
• The query uses a combination of the Format and Year functions, along with a sub query to rank the records and derive the RequestNumber.
  
• This method can run slowly on a large table, so you way want to use another query to limit the record set to a given month, or maybe a given year, then use that as the source for the query that generates the RequestNumber.