Case Number by month and year - Sequential Annual Coding in Microsoft Access


	Back to Sequential Annual Coding Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Case Number by month and year

Art Vax

2 years ago

Hey!
I have a custom ticket system in Access that allows users to contact us, the support team that accepts the tickets fills in a form with textboxes that then gets sent to the ticket list table and fills the table accordingly.

I want to make a custom Case Number to each ticket (a record) by having it being yymn format (i.e: 23624 - the 24th ticket in June 2023, 2361 - the first ticket in June 2023, 236107 - the 107th ticket in June 2023, etc..)

I don't want to use Autonumber ID because it will be difficult to the administration team to track them down.
And I don't mind the tickets being in this format since they're only for the staff and not the users and customers.

Would appreciate any help!

	Richard Rost @Reply 2 years ago
I've been planning to make a followup to this video with a few additional pointers. I'll add it to the list. Stay tuned.

	Kevin Robertson @Reply 2 years ago
Here is what I came up with (see screenshot). It appears to work but I only added 10 records (last month and this month) to my Table. Full testing with a range of months and years is advised.

	Kevin Robertson @Reply 2 years ago

	Art Vax OP @Reply 2 years ago
Hey there! The code you provided indeed works for the first time a ticket is made, but when another ticket is being made the code above makes the CaseNumber the same (it doesn't increment it) I looked into the code and the code is exactly the same..

	Art Vax OP @Reply 2 years ago
Thinking about it more, it might be because the EventCase field for me still has null values so the Nz function always returns a 0 resulting in currentCounter always being 1, but there are over 600 records at the moment to fill them all in manually, I would like to run a query to update the field in the table, but how would I make a query with all of these conditions?

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

Next Unseen