Incrementing number that needs to reset weekly

[franc75]

Hi all - Need some help!

I am creating a database to keep a record of incoming orders. My main table has the following fields, which is linked to a form for easy input:

Ref - (Autonumber) for a unique reference
Date - (Date) Order Date
Customer - (Text) Customers Name
Details - (Text) - Description of Goods
Pcs - (Number) - No Items
Weight (Number) - Weight

No problem there. The difficult bit is, (and I'm not sure how best to explain this) ...

I now want to add another field: OrderNo, which will start at the beginning of the week (say, Monday) at zero, and increase by 1 automatically each time a new order is added. These numbers are to remain as part of the record permanently, but come next Monday, I want to start from zero again for that weeks orders and so on.

Does anyone have any ideas how to implement this automatically when the record is created ?

 

[DCrake]

Simple Software Solutions

What you gonna do if the computer is not used on a Monday, lets say it's a Bank Holiday?

 

[franc75]

I suppose it will start from zero on the Tuesday. Really, all it would need to do would be to reset every 7 days.

 

[chergh]

You could create a query that gets the week number and year for the last record entered. You could then compare it to the current week using the date function.

Then using an if statement if the week numbers are the same the record number is incremented by one otherwise it starts again from 0.

 

[DCrake]

Simple Software Solutions

Ok Lets begin

Create a new table with one field in it named fldLastResetDate

Then enter Last Mondays date in as an initial value

Next on the start up screen of your app get it to test if the date in the fldLastResetDate is older than 6 days.

i = DateDiff("d",fldLastResetDate,Date)

If i >= 7 Then

..... change the date in the field to todays date, or the first monday in the current week.

.... Reset you counter back to zero

Endif

Next time you add a record to the table get the order number from the last record added and add 1 to it.

May need a little manipulation to suit your needs but the logic is there.

Code Master:http://www.icraftlimited.co.uk

 

[franc75]

Thanks for your help guys - You've given me something to think about!

 

[GolferGuy]

If you are doing this in a multiuser environment, I would suggest that as soon as you determine what the next sequencial number is that you add that record to the file BEFORE you return that number to the caller. This is to prevent two people getting the same number. If you have more fields needed to create the record than just this sequencial number, then I would suggest that you have a separate table that you keep these sequencial numbers in and it would be in this "sequencial number table" that you would add the next record before passing that next sequencial number back to the caller. By adding the record at the time of calculating the next sequencial number, the function doing this action will be able to have error code in it to realize that after calculating the next sequencial number, someone else has already gotten that number and used it because it is already in the table. Therefore, the error routine can just calculate the next sequencial number again, add that record to the table and pass the number back to the caller.