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