Solved Auto yearly and monthly numbering (1 Viewer)

nirmal

Member
Local time
Today, 17:48
Joined
Feb 27, 2020
Messages
38
How to start renumbering from a certain date on monthly and yearly level in a append query. So for every year, for example 1st April to 31st March we get a new serial number and similarly on monthly ground from 1st date of every month.
Every Year on 1st April the Yearly number should reset to 1 and
similarly Every Month on 1st day of that month it should reset to 1
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 05:18
Joined
Oct 29, 2018
Messages
16,599
You would need to use a custom function for that. Try to do a search on "custom autonumber."
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:18
Joined
May 7, 2009
Messages
14,156
if i recall i already gave the code to you on your db.
if not working, you upload your db again.
 

nirmal

Member
Local time
Today, 17:48
Joined
Feb 27, 2020
Messages
38
if i recall i already gave the code to you on your db.
if not working, you upload your db again.
Sir I was unable to use the function you gave me
Sir I am using dd/mm/yyyy format
 

Attachments

  • SampleDb1.1.accdb
    636 KB · Views: 144

nirmal

Member
Local time
Today, 17:48
Joined
Feb 27, 2020
Messages
38
sir similarly can we have a autonumbering on daily basis too
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:18
Joined
May 7, 2009
Messages
14,156
see your customer_form
 

Attachments

  • SampleDb1.1.accdb
    744 KB · Views: 150

nirmal

Member
Local time
Today, 17:48
Joined
Feb 27, 2020
Messages
38
Sir the yearly and monthly autonumbering is working fantastic
Sir only now I require the daily autonumbering. Means a new column of Daily numbering

Secondly Sir, about that progressive Yearly number, it is taking the overall progressive from previous years too.
I want only progressive for the year in consideration. (Check out the report)
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:18
Joined
May 7, 2009
Messages
14,156
it is taking the overall progressive from previous years too.
because of your "fiscal year" (which starts on 1 april).
every 1st of april, the yearly count will reset to 1.
that is what you told us (post #1).
 

nirmal

Member
Local time
Today, 17:48
Joined
Feb 27, 2020
Messages
38
because of your "fiscal year" (which starts on 1 april).
every 1st of april, the yearly count will reset to 1.
that is what you told us (post #1).
Sir my report page requires a fiscal figures of progressive customer details as in the report, but it should take only current fiscal year progressive counts. Sending the screen shot of report where the maternal age > 35 should be showing 0, instead of 11 (which are the previuos years figures)
similarly Others should be showing 61 (instead of 507) and Total Customers should be showing 61 (instead of 518)
 

Attachments

  • screenshot of Sample Db_LI.jpg
    screenshot of Sample Db_LI.jpg
    549.8 KB · Views: 40

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:18
Joined
May 7, 2009
Messages
14,156
OK, check the report and the query.
 

Attachments

  • SampleDb1.1.accdb
    768 KB · Views: 49

nirmal

Member
Local time
Today, 17:48
Joined
Feb 27, 2020
Messages
38
Sir today I was testing the Db and I noticed some error in the yearly progressive number.
There were 61 customers in the month of April, 109 in May, 95 in June and 6 in July 2021
so progressive of april is showing 61, May 170, but in June it is showing 258 instead of 265 (61+109+95) again in July it is showing correctly 271
I tried a lot to find the error, with no result.
So Sir help me

Sending theDb
 

Attachments

  • SampleDb1.1 (4).accdb
    928 KB · Views: 135

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:18
Joined
May 7, 2009
Messages
14,156
ok, check and test
 

Attachments

  • SampleDb1.1 (4).accdb
    836 KB · Views: 129

nirmal

Member
Local time
Today, 17:48
Joined
Feb 27, 2020
Messages
38
Sir, now the error seems to be solved. But I want to know the reason of error
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:18
Joined
May 7, 2009
Messages
14,156
the reason is that it does not correctly
filter the table.
 

Users who are viewing this thread

Top Bottom