Access Order ID similar to ID AutoNumber,


I am trying to create a table with filed Order ID similar to  ID AutoNumber, but this number should count ascending whenever the delivery date is the same.

for example if i have, 2 orders delivery today the filed should be count 1, then 2

if i have 1 order for to morrow this field value should be 1

Attached is basic sample data of what i am planing to achieve .

Thank You
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I think if you set primary key merging both columns you can do this with condition.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You _might_ be able to do this with a combination of a custom function plus the new Data Macro feature of the table, assuming you're running 2010 or later, and you're using the .ACCDB format.

You can use the "If" macro to check for [IsInsert], and then fire your custom function to return the count of the records with the same OrderDate. So my custom function might look something like this:

Function GetOrderCount(OrderDate As Date) As Integer

GetOrderCount = DCount("ID", "Table1", "OrderDate=#" & OrderDate & "#")

End Function

Open in new window

And my Data Macro would use that:

OnChange Data Macro
I've attached a database with a single table and a single module. The Table has an OnChange data macro that will set the value of a field based on the OrderDate. So if you add a new record to the table, and set the new record's OrderDate to the same Date as an existing order, the macro sets the value of Field3 to the OrderID - [Count].
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted>

Note that with a system like this the "highest" number will not always be the same as the "count"

For example:
You have 3 orders for today so far:
...then, for any number of reasons, you have to delete order "2".
So now you have "two" orders for today, ...but the highest number is "three"

With systems like this, (and even with standard autonumbers) is easy for someone to presume that the highest number will always be the same as the count.

So, you still need Scott's function to get the actual order "Count" for each day.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

You also have to consider the first record for a date, so something like the following is what you need.

GetNextOrderSeq = Nz(DMax("SeqNum", "Table1", "OrderDate=#" & OrderDate & "#"), 0) + 1

I don't know if this will work as a DataMacro since I don't use them.  Most of my BE's are SQL Server rather than ACE.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Actually, the function I posted should probably be this:

Function GetOrderCount(OrderDate As Date) As Integer

GetOrderCount = DCount("ID", "Table1", "OrderDate=#" & OrderDate & "#") +1

End Function

Open in new window

This will include the "-1" value for the FIRST order added for a Date, and will then continue on to add others. That said, the use of a Sequence value, as suggested by Pat, might be a better solution since it would take into account removed records (if you can remove records once they're added).
DCount() fails if records can be deleted.  DMax() is safer.  If no record is ever deleted, both will return the same result.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Agreed, but ONLY if there's a "sequence" column. Otherwise, DMAX on the Date column would just return the most recent Date.
Isn't an ID (sequence number) what we are trying to generate here?
MaherioAuthor Commented:
Sorry Gents

I feel all answers above my understanding or knowledge.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What do you not understand about my post - the one with the screen shots, coding function AND attached database that shows you exactly how it works?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MaherioAuthor Commented:
Sorry Gents i stopped working on this project. Thanks for all contributions
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.