By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

Reset file number to zero each year

P: 3
I built a file management database which generates a file number based on the year.

i.e. 2006-PRRS-13

The criteria was that each file should be unique. So I took the primary key, added one to it and moved on.

Several months down the road they came back and said did we mention the file number needs to reset to zero each year.

They click a button to generate the file number.

This sql query (below) gives the result I want but I either need to implement a similar approach in code or take the value from the query, add one and put it into the form. Either would solve my problem.


SELECT Count(Left([file_no],4)) AS Expr1
FROM tbl_item
WHERE (((Left([file_no],4)) Like Year(Now())));


I have also tried:

dim Now_year as Integer
now_year = Year(Date)

NumberOfFiles = DCount("FILE_NO", "tbl_item", "Left([file_no],4)" = now_year)
MsgBox NumberOfFiles

Similar approaches were taken with DMax. I was unsuccessful.

Any suggestions would be greatly appreciated.

Sean
Jan 4 '07 #1
Share this Question
Share on Google+
11 Replies


Expert 5K+
P: 8,434
How do you implement the "PK+1" at present?
Jan 4 '07 #2

missinglinq
Expert 2.5K+
P: 3,532
I think the general strategy/logic here needs to be:

1) Record the current year somewhere

2) In the form open event the appropriate form compare the current year with the last recorded year

3) If the two are equal, continue your present scheme for setting the file number

4) If the two are not equal, change the year part and reset the file number part to 1

You can stuff the year into a field in a utility table, if you have one or simply make up a single record/single field table for that purpose. I always make one for things like this as well as recording company data for including in letterhead of reports, etc.
Jan 4 '07 #3

P: 3
Thank you for the responses.

Currently I set the id by getting the primary key of the new record.

It is a tabbed interface. They enter some information on the first tab and move to the second to generate the file number. This movement to the next tab saves the file and creates the primary key which I then retrieve from a hidden control on the generate file number tab.

Expand|Select|Wrap|Line Numbers
  1. Dim strItemId As String        
  2. strItemId = Me.item_id.Value
  3. strItemId = Year(Date) & "-PRRS-" & strItemId
  4. Me.file_no_tab.Value = strItemId
  5.  


I found a similar problem answered under the search:

Reset file number for new year
http://www.thescripts.com/forum/thread440116.html

I was curious how the utility table approach would manage the count of files for the current year?
Jan 4 '07 #4

missinglinq
Expert 2.5K+
P: 3,532
The utility table is simply used to store the current year so that you have something to check against each time you open your form. If the last time you made a new record was 12/28/2006, then 2006 would be stored in the field. You open your form up on, and go to enter a new record, you check the stored field against the current year. If the two are the same (say the date is now 12/30/2006) you increment your record number as always. If the two are different (say the date is now 1/2/2007) you change the year part of your record number and reset the tag end of it to 1.
Jan 5 '07 #5

Expert 5K+
P: 8,434
The utility table is simply used to store the current year so that you have something to check against each time you open your form. If the last time you made a new record was 12/28/2006, then 2006 would be stored in the field. You open your form up on, and go to enter a new record, you check the stored field against the current year. If the two are the same (say the date is now 12/30/2006) you increment your record number as always. If the two are different (say the date is now 1/2/2007) you change the year part of your record number and reset the tag end of it to 1.
I'm a bit confused, as you seem to be using the expression "current year" to refer to two different things. When you say the utility table is used to store "the current year" do you mean the year when we generated the last key? If so, surely that's already encoded in the key and wouldn't really be needed. (It might be more convenient though, to store it here redundantly).

The current year (the "real" one) should not need to be stored at all, as it is available anywhere, any time.
Jan 5 '07 #6

missinglinq
Expert 2.5K+
P: 3,532
I built a file management database which generates a file number based on the year. i.e. 2006-PRRS-13

The criteria was that each file should be unique. So I took the primary key, added one to it and moved on.

Several months down the road they came back and said did we mention the file number needs to reset to zero each year.
In order to assign a new file number, the db has to know whether or not the tail end of the number needs to be reset to zero. The only way to know this is to determine whether or not the beginning of a new year has occurred between the time the last new record was originated and the time you're originating the next record.

If the year of the last record is the same as the new record, a new year has not occurred, and you simply increment your file number.

If the year of the last record is not the same as the new record, a new year has occurred, and you need to reset your file number to zero.
Jan 5 '07 #7

missinglinq
Expert 2.5K+
P: 3,532
Forgot to add, if a new year has occurred, you reset the file number to zero AND you change the stored year to the "new" year.
Jan 5 '07 #8

Expert 5K+
P: 8,434
Forgot to add, if a new year has occurred, you reset the file number to zero AND you change the stored year to the "new" year.
Of course, you could probably also get away with just retrieving the last key value form the main file, rather than storing anything in a separate file. Whether that's good database design, I couldn't say.
Jan 5 '07 #9

missinglinq
Expert 2.5K+
P: 3,532
As my signature says, There's Always more than one way to skin a cat!
Jan 5 '07 #10

Expert 5K+
P: 8,434
As my signature says, There's Always more than one way to skin a cat!
True. I was just saying in a post yesterday, given any task to perform, any two programmers will almost always find different ways to do it.

Then argue about it. :)
Jan 5 '07 #11

NeoPa
Expert Mod 15k+
P: 31,494
Then some other beggar comes in to complicate matters even further...
Expand|Select|Wrap|Line Numbers
  1. strNewCode = Year(Date()) & _
  2.              "-PRRS-" & _
  3.              Nz(DMax("Val(Mid([file_no],11))", _
  4.                      "tbl_item", _
  5.                      "Val(Left([file_no],4))=Year(Date())"),0)+1
Jan 6 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.