custom number format

[xzquala]

just out of curiosity?

is it prudent to have a "new" database created for each year?

I know it is more work on the programmers side, but it does remove the possibility of duplicate serial numbers with different years not importing properly.

Also, would it not be more efficient for the server, since past years are much less frequently inquired upon?

Or is that whole concept generally considered a great big "no, no, no, no, no!!!!!"?

 

[Alexandre]

I believe it is a huge overkill since Pat's formula (for an auto incremented number that does not need to be unique at the opposite from an autonumber) would work, AND a no no no since it breaks the logics of related data in a DB (you would find very hard to query on related records from different years/databases).
I don't really have experience of really big databases, but I believe that if you've reached a point where serving data is a bottleneck, you should think about upgrading to SQL server / migrating to Oracle.

 

[MsLady]

~Subscribing to thread

 

[MsLady]

Hello all,
I have an identical issue i am trying to accomplish here. I have read all the demerits and i know waht the book says and what you guys advice but this needs to get done this way for the best reasons - break the rules a little

So, I have choosen Pat's method, where the customer autonumber restarts at the beginning of every year (although im looking to restart everymonth), but mine is not restarting, i can't seem to figure out why?

I have two fields "timeInMY" (i.e. monthyear, mmyy) and "JobSequenceId", and two controls bound to them on my form.
I set the form control default value of timeInMY to "Format(Month(Date())) & Right(Format(Year(Date())),2)"

I want the JobId in this format of mmyy-0001.
It works, but the numbering just continues like 806-0001, 806-0002, 906-0003
Why doesn't it restart at the beginning of a new month?

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim nextNum As String, myMonthYear As String
myMonthYear = Format(Month(Date)) & Right(Format(Year(Date)), 2)
If Me.NewRecord Then
On Error Resume Next

nextNum = Format(Nz(DMax("[jobIdSequence]", "[tblJobDetails]", [timeInMY] = myMonthYear), 0) + 1, "0000")
Me.jobIdSequence = nextNum
Me.jobId = myMonthYear & " - " & nextNum
End If
End Sub

Note: I have an autonumber primary key i call sysID which is different from this JobId. This jobId will be used for searching purposes I am not using the sysId cos it has grown tremendously and will continue to grow.

Thanks in advance for ur assistance

 

[MsLady]

nextNum = Format(Nz(DMax("[jobIdSequence]", "[tblJobDetails]", "[timeInMY] = " & myMonthYear), 0) + 1, "0000")

If myMonthYear is text, you need to enclose it in quotes:

nextNum = Format(Nz(DMax("[jobIdSequence]", "[tblJobDetails]", "[timeInMY] = '" & myMonthYear & "'"), 0) + 1, "0000")

Note that the format() function is making your NextNum a string. That may cause other problems. You seem to be confusing formatting with data storage.

Works like charm!!!

I took out the format(). You rock buddy!

 

[RuralGuy]

Ms. Pat Hartman has posted tons of excellent code over the years. She earned her MVP status.

 

[billyr]

I have also been in this bucket of worms. I converted a big Excel application to Access and the user insisted on keeping their, user generated, record IDs for staff, clients, facilities, resources etc. I was very new to Access. I made a table to hold the last count for each and a public function to create new IDs and increment the counter(s). What a mess! Like an idiot, I used these IDs as the key fields. Hind sight, albeit painfull, is 20/20. I should have use an autonumber field as the record ID and key and the user's record "ID" - just another field to display, sort, or what have you.

 

[statsman]

I downloaded this off the forum some time ago. The paper is yellow its so old but it works great.
I don't recall whos code it is but it works like a charm for year and sequence. If you recognize it please claim it.

(quote)

Create two fields in your table:
TheYear - long
TheSequence - long

Then enter this code in your form's BeforeUpdate

Me.TheYear = CLng(DatePart("YYYY", Date()))
Me.TheSequence = Nz(DMax("[TheSequence]", "YourTableName", "[TheYear] = "&&Me.TheYear),0) +1

Thats all the code you need. When a new year starts the DMax function returns a Null, which the Nz function changes to a 0. Then it just adds 1 to get the next value.

Why two fields? You will spend a lot of time writing code to continuously tear them apart. Its much easier to store and group them when they're stored separately.

Its also easier to format them in a query.

(end quote)

What happens is when you make your first keystroke on the form the year and sequence are entered automatically. So if your last entry was 2006 14 the next will be 2006 15.

 

[Turbollama]

I can show you a method to avoid such gaps but you should keep in mind that it is not 100% reliable in multi-users and replication environments.

First, I have been reading through this thread and it has made a lot of sense. Thank you to everyone for their contributions. I do need a bit more help along this topic. My supervisor asked me to create something similar to what was mentioned above however I need to avoid the gaps in the methods suggested previously.

ie:
2006-0002
2006-0003
2006-0004

Then say if someone does not complete 2006-0004, it would automatically start with 2006-0004 the next time someone used the program to enter a record. The year also needs to automatically reset and if possible he would like the ID# to reset at the beginning of each year.

ie:
2006-0186
2006-0187
2007-0001
2007-0002

A good thing is there is only one computer that has access to this program and will be allowed to add/edit data so no multi-user issues should occur. What would be the best way to do this? Any help would be greatly appreciated.

 

[neileg]

What would be the best way to do this? Any help would be greatly appreciated.

The code that statsman gives in the last post is what you need.

 

[Turbollama]

I will give that a shot. Previous examples seemed so much more complex, I'm surprised that something so simple should work. Thank you for pointing it out, I would have passed it by otherwise.

Edit to add: I tried the method mentioned above, and it did not work. The ID number kept adding sequentially (leaving gaps) even if a record was cancelled or incomplete. Also I need to keep the ID # and Year in the same field as mentioned previously in this thread. Is there another way to accomplish what I am looking for?

 

[Turbollama]

I apologize for bumping this, but I am still in need of help as mentioned in my previous post on 9/26/06. As for tearing the fields apart, we will not be doing this with the database. It is strickly an ID number for tracking purposes.

Please help, I need to accomplish this soon.