Reset Number every Month (1 Viewer)

zezo2021

Member
Local time
Today, 16:53
Joined
Mar 25, 2021
Messages
381
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

Super Moderator
Staff member
Local time
Today, 14:53
Joined
Feb 19, 2013
Messages
16,555
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

Immoderate Moderator
Staff member
Local time
Today, 09:53
Joined
Feb 28, 2001
Messages
27,003
Look at these posts




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

Super Moderator
Staff member
Local time
Today, 10:53
Joined
Feb 19, 2002
Messages
42,984
Here's an example that might be useful.
 

Attachments

  • CustomSequenceNumber20210303.zip
    93.7 KB · Views: 563

zezo2021

Member
Local time
Today, 16:53
Joined
Mar 25, 2021
Messages
381
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

..forever waiting... waiting for jellybean!
Local time
Today, 22:53
Joined
May 7, 2009
Messages
19,175
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

Super Moderator
Staff member
Local time
Today, 10:53
Joined
Feb 19, 2002
Messages
42,984
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

Banishment Pending
Local time
Today, 09:53
Joined
May 11, 2011
Messages
11,613
Based on your initial post, this expression should do what you want, including the year:

Code:
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

Member
Local time
Today, 16:53
Joined
Mar 25, 2021
Messages
381
Based on your initial post, this expression should do what you want, including the year:

Code:
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


(y)(y)(y)(y)(y)(y)(y)(y)(y)(y)(y)(y)(y)
 

Users who are viewing this thread

Top Bottom