custom number format

[ksgirl]

Alexandre, your English is great, and I am learning so much more than I would have on my own. I also understand why I have to make two separate fields to achieve what I want. My only concern is when I input the old data into the system. Right now, I am just making a temporary database, with only a few records (lets say 10 or so) in each of the tables. To get all the gliches worked out before we input all the old data...... With these Clearance ID's, they are generated at random (or incremented, I'm not sure) for each year. So, for example, 02-05873 might be similar to the 98-05873, with the year just being different. I don't believe this occurs very often, and yes they are in our terms "duplicate" numbers, so that is why I have been asking all these questions. I guess what I'm really wanting, I can't seem to achieve. I am needing the user to have to type in the whole "00-00000" format, so that they can access all the records. I believe with Pat, that in assuming that autonumber will not be useable in my case, but what other options do you think I have? Do you see my frustration? I haven't attempted any of your suggestions yet, but I will tackle them this morning, and of course let you know my progress! Thanks again.
~Nicole

 

[Pat Hartman]

The users never have to "enter" the primary key. For purposes of finding a record for a form, use an unbound text box. Let them type in 01-12345. Then they can push a "find" button and your code will separate the string entered in the text box into two fields that will match the two stored fields. You can use the Left() and Right() functions to extract portions of a text string.

For the conversion effort. Import the data as is into a tempory table. Then create an append query that uses the Left() and Right() functions to separate the id field and appends all the records to the permanent table. Once you're done. Just delete the temp table and compact the db.

 

[ksgirl]

AHHHHHHHHHHHHHHHHHHHHHHHHHHHHH! You don't know how happy I am that it works!!! I made some adjustments such as:
with the unbounded 'txtSerial' text box so that it displays "00-00000" (it was dropping the leading zeros on the autonumber field) I changed the control source to =[txtYear] & "-" & Format( [txtClearance ID], "00000")....and everything works like a charm. Thanks for all your help!!!!!!!!!!! Now I have to go through all the other forms I have, and change their properties to display this new ID....so it is going to take me a while, but I'm glad I had your expertise in helping me solve this extremely frustrating problem!!!!! I am going to leave the conversion suggestions for who ever takes this project over, that you (Pat) suggested, but you might see someone on here in a couple months wanting some advice!

Thanks again!
~Nicole

 

[Alexandre]

Before going for an autonumber (as per my suggestion) instead of Pat's incrementing number formula, did you take into account the point Pat just made that if your orginal (old) dataset may contains serials with an identical right part for two different years (ie 01-00006 and 02-00006) then using an autonumer would not allow you to import your old data in your new DB? (since autonumbers must be unique)

 

[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

 

[Pat Hartman]

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.

 

[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.

 

[Pat Hartman]

Thanks, it is nice to occasionally be appreciated.

 

[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.

 

[Pat Hartman]

It's never to late to clean up the mess. Every new object you create, just makes the pile of corrections higher. Take a couple of days (it shouldn't take more than one or two if you are organized about the changes) and fix it now.

 

[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.

 

[Pat Hartman]

But YOU will need to tear the fields apart to work with them. It is also better for indexing purposes since you want to search for = on the year field and max() on the sequence number.

Post your code and tell us what event you placed it in.