Reset Number every Month

[zezo2021]

Hello
I have this expression
Number#: Month([OrderDate]) & "-" & DCount("*","Q_Leadger_Input","LedgerID<=" & [Tbl_Leadger].[LedgerID])
I need to reset the number with every month
for example
04-1
04-2
04-3
etch
05-1
05-2
05-3

how can I do the reset with this code

 

[CJ_London]

include a reference to the year/month as part of your dcount criteria. Not enough info provided so can only speculate how that might work. You make things difficult for yourself by having a combined number and no idea what you will do next year when the same month comes around again.

 

[The_Doc_Man]

Look at these posts

Format [AutoNumber] & Restart at particular condition

Dear All, I have a Parent/child tables... Parent Table: Sales (SNo) Child Table : Sales_Detail (SNo) Child Table : Sales_Payment (SNo) Child Table : Sales_Return (SNo) Each Parent Record has multiple Child records in each table.... Each Child Table has column SrNo column with AutoNumber...

www.access-programmers.co.uk

Solved - Autonumber Advice

Hi, I have created a company stock database which is working well but I am looking to expand its functionality to more of a customer orders database with the stock side of things being a feature rather than its primary use. I have been updating the customer order numbers manually based on...

www.access-programmers.co.uk

Custom ID Entry help

Hi! Hopefully someone can help me here. I was given a project from my MGR and have made it more complicated then I first intended. I am creating a database for leaders throughout the company to log defective product. When each entry is created I would like to assign them a unique ID code that...

www.access-programmers.co.uk

Somewhere in there you should find something helpful. There are other posts as well, but these three will get you started. If you want more, use the forum's Search feature for "Custom Autonumber" and you will get many responses.

 

[Pat Hartman]

Here's an example that might be useful.

 

[zezo2021]

I need to reset the Serial expression every month
Number#: Month([OrderDate]) & "-" & DCount("*","Q_Ledger_Input","LedgerID<=" & [Tbl_Ledger].[LedgerID])

For Only one YEAR use

 

[arnelgp]

you need to Save the serial to your table:

Number#: Month([OrderDate]) & "-" & Nz(Dmax("SerialField", "Q_Ledger_Input", "SerialField Like '" & Month([OrderDate]) & "-*'"), 0) + 1

 

[Pat Hartman]

You need to include Month AND Year in the criteria and as arnel has pointed out, you MUST use dMax() and NOT dCount() to ensure that you get an accurate result. If you use only the Month, your sequence number will continue across multiple years so if your count got up to 124 for March of last year, it would start this year as 125 which doesn't sound like what you want.

Please examine the sample I posted.

 

[plog]

Based on your initial post, this expression should do what you want, including the year:

MonthNumber: Month([OrderDate]) & "/" & Year([OrderDate]) & " -" & DCount("LedgerID","Q_Leadger_Input","[LedgerID]<=" & [LedgerID] & " AND Month(OrderDate)=" & Month([OrderDate]) & " AND  Year(OrderDate)=" & Year([OrderDate]))

And a ton of questions/notes:

1. Why? What's the purpose of this? What will this number do for you?

2. You used "Q_Leadger_Input" in your initial post but omitted the "a" in your subsequent one.

3. Does the "Q" in Q_Leadger_Input" mean its query? The Dcount should be based on a table if possible--its going to take a while to run with a lot of recrods and building it on a qeury will make it even more ineffecient.

4. "Number" is a reserved word (https://docs.microsoft.com/en-us/office/troubleshoot/access/reserved-words) and shouldn't be used as a name, I renamed the field "MonthNumber.

5. How are you sure this will be just 1 year of data? If you plan on clearing it out and making a new database next year, that's a very poor way to manage your data.

6. LedgerID must be unique or this will throw off your numbering.

7. This would be 279% easier if you built a report and had the report number your records.

 

[zezo2021]

Based on your initial post, this expression should do what you want, including the year:

MonthNumber: Month([OrderDate]) & "/" & Year([OrderDate]) & " -" & DCount("LedgerID","Q_Leadger_Input","[LedgerID]<=" & [LedgerID] & " AND Month(OrderDate)=" & Month([OrderDate]) & " AND  Year(OrderDate)=" & Year([OrderDate]))

And a ton of questions/notes:

1. Why? What's the purpose of this? What will this number do for you?

2. You used "Q_Leadger_Input" in your initial post but omitted the "a" in your subsequent one.

3. Does the "Q" in Q_Leadger_Input" mean its query? The Dcount should be based on a table if possible--its going to take a while to run with a lot of recrods and building it on a qeury will make it even more ineffecient.

4. "Number" is a reserved word (https://docs.microsoft.com/en-us/office/troubleshoot/access/reserved-words) and shouldn't be used as a name, I renamed the field "MonthNumber.

5. How are you sure this will be just 1 year of data? If you plan on clearing it out and making a new database next year, that's a very poor way to manage your data.

6. LedgerID must be unique or this will throw off your numbering.

7. This would be 279% easier if you built a report and had the report number your records.

Thanks for the solution

 

[Pat Hartman]

This would be 279% easier