Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Home > TechHelp > Directory > Access > Sequential Numbers < Deleted AutoNumber | Customer Codes >
Back to Sequential Numbers    Comments List
Im Stuck Upload Images   Link   Email  
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.


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 3/4/2024 7:50:39 AM. PLT: 0s