Free Lessons Courses Seminars TechHelp Fast Tips Templates Topic Index Forum ABCD

 Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon More... What's New? Popular Links ------------ Access Courses Access Index Access TechHelp Access Forum Access Troubleshooter ------------ Excel Courses Excel Index ------------ Code Vault Testimonials Tip Jar

 Home > TechHelp > Directory > Access > Sequential Numbers < Deleted AutoNumber | Customer Codes >
 Back to Sequential Numbers    Comments List
Sandra Truax
I'm a Pug Breeder. For each litter, the pup is given a number, for example 22-001 is the first pup of the year 2022.  I can't figure out how to make the sequential numbering work for this format of year they are born (WhelpDate) and the sequence they are born. Can someone help?
 Adam Schwanz Use Left or Right or both depending how you want to set it up. String Functions then use DMAX to get the biggest number and increment DMax You might need to manipulate the data a little more depending what you do with it but all that you should need should be covered in string functions.
 Richard Rost Use a counter. Keep it in a separate field. Save the date of birth in a field. Now it's easy to put the two together.
 Adam Schwanz Oh yea .... Pfft who wants to do it the easy way.
 Scott Axton Where are you getting stuck? OK I might approach this a little differently than Adam did. In Access there is often more than one way to arrive at a solution.  I want to help you think it through.   You want to "build" a new field say - PuppyCode as Short Text. You aren't doing math on it should be text. Just take the year of WhelpDate ( DatePart ) and concatenate the dash ( Concatenation ) and then maximum of the SeqNo +  1 then Format that to 3 places. You could litterally (see what I did there?) make a "PuppyCode" that would tell you more. Somethings you could think about.   Is your Sequence the total overall for the year of all the litters?   Do you want total for that paring of sire and dame? Add in Gender:  M or F -  OK   ---- 22-004-F,   22-005-M You could have fun with this depending on the fields you have.  The big idea is separate the pieces then build the string like you want.
 Scott Axton See there you have it - 3 solutions to the same question. (Why don't I refresh before posting?) By your post, I assume that you start over at 1 for each year.  That would be a different test to see if there were lower numbers before resetting.  Or you could just "seed" the first of the year and go from there.
 Richard Rost I smell a video coming...
 Sandra Truax I should have been checking back here before struggling with it.  I'm going to give this a try, because I am NOT getting anywhere. I did create a new field called YrBorn and used the Right([DateWhelped],2) in an update query to get the years, but when I Dmax the YrSequence, it give me the highest for all the years, and not the current year. I had been trying to include a WHERE in the Dmax, but realize after reading the above, I was just chasing my tail!  Going to give your suggestions a try now. Richard, I look forward to a the video!
 Sandra Truax Scott, yes I start over at 1 each year.
 Scott Axton RE:  "I should have been checking back here before struggling with it." That's half the fun and how you learn. Challenge for you:  How do you think you might automatically start the number at 1 for a new year?
 Sandra Truax Last try before I give up and wait on the video.  When I used a StatusBox for the code, it worked.  But when I put the following on both the form and in the subform, it will NOT generate the new code when a new entry is made.  Do you mind telling me what I'm doing wrong?  I know my field names are not like I'm learning to make them now, but I've been using this database for about 14 years. Private Sub Form_BeforeInsert(Cancel As Integer)     Yr_Seq = DMax("yr_seq", "tblpuppies", "[Yr_Born] = Right([Date Whelped],2)") + 1 End Sub
 Adam Schwanz Is Yr_Born only 2 digits? Remember Concatenation you are putting a value in. "[Yr_Born]=" & Right([Date Whelped],2)+1)
 Sandra Truax Adam: Yes it is a two digit year. When I tried what you put it says "Syntax error (missing operator) "[Yr_Born]=".  Yr_Born is a Short Text field. Scott: I'm thinking some how I would reset [Yr_Seq]=0 some how, but don't have a clue how to make it do that automatically at the beginning of a new year, or if that is even right. I have the Yr_Seq field set as short text. Could that be why I can't get it to work?
 Adam Schwanz so Yr_Born is short text? I think these are usually easier/cleaner to convert inside of a query, I would make a query at this point and do like `YrBornInt: CInt(Yr_Born)` and while you're at it, make another one `RightWhelp: Right([Date Whelped],2)` Then just do in the code Yr_Seq=DMAX("[Yr_Seq]","NewQueryWeMade","YrBornInt=" & RightWhelp)+1
 Sandra Truax Hallelujah!!!  Thanks to Adam I got this to work.   Adam, I tried the code you sent, and it kept giving me an error, so I tried      Yr_Seq = DMax("[Yr_Seq]", "NewPupsQ") + 1 and this worked!  I guess putting the rest in the query is all I needed!!! Thank you SOOOOO much! Now all I have to do is figure out how to format it to be a three digit number.  But that's going to be a project for tomorrow! I'm thrilled with this!
 Kevin Yip DMax() returns null if no records satisfy its criteria, such as when no puppy has yet to be born for that year.  So you need to check for that, such as with Nz().
 Scott Axton Sandra just add the format to the results of your year sequence. And as KY mentioned if you don't want to return a null wrap it in NZ ``` Yr_Seq = Format(NZ(DMax("[Yr_Seq]", "NewPupsQ") + 1,0),"000") ```
 Sandra Truax Thank you Scott! As to the challenge you gave me, I'm think if I tweaked the query Adam had me build to pull all pups for the current year, then add the line IF IsNull(Yr_Seq) Then Yr_Seq = 0.  That way when it adds 1 I will have the first pup as 1.  Should that work? But then again, after looking at your code, will that set it to zero for the new year, because I'm thinking... yes.
 Richard Rost Wait one hour. I'm making a video. :)
 Scott Axton I haven't tried it but I believe you are going to have to bring the Yr_Born into the mix. So if you just had a liter today you want to enter the next number correct? Say you've had 38 puppies this year and the next one would be 39. so:  right now = "22-039" Then in January 1, 2023 you want to start new. Now if you want to start the number over for each year On the 1st it would be Yr_born AND Yr_seq so that should return "23-000" If you don't have the year also the dMax would keep increasing. Now on the 2nd you would have your first litter so the first pup should be "23-001" Try it and see what you come up with.  (Run it in a TEST /copy db)
 Scott Axton ^^ Of course Richard is.  Darn refresh. See if you come up with the answer before he posts the video.  (Another challenge)
 Richard Rost Oh, it only took me 3 minutes to write the code. The time consuming part of making a video is all the rest of the stuff... (PP slides, keywords, writing it up, editing, upload, posting it, etc.) and I gotta feed the dogs here in a minute... patience!
 Richard Rost Here ya go... it's a Sunday Surprise (I never release videos on Sundays. LOL). Sequential Annual Coding
 Scott Axton Very nice Richard  I learned today too!  I especially liked how you addressed weeding out writing the code for those not assigned a customer since and and not over writing those that had already been assigned a code. Thanks to you too Sandra, this was a fun little thought project.  It really does amaze me how many ways there are to solving a challenge.
 Sandra Truax Scott, I didn't get a chance to meet your challenge because I was in church!  Thank you Richard for the video! And I want to thank Kevin Yip and Adam also for helping me with this. Y'all are the best!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Sequential Numbers.