Reset a field when a new day starts

[ashraf_al_ani]

Dear All
i have a field called num1 type number
is there a way to let that field start from 1 when a new day starts??

so that field with every record will be increased by 1
then it will be reset to 1 when a new day statrts

best regards

[ranman256]

If you are talking about an autocounter and entering new data into a table,
if your entry table is called tEntry
have a copy of tEntry called tEntryMT (an empty table with autocounter field)

start the day by copying tEntryMT to overwrite tEntry
enter all data into tEntry (autocounter will start at 1)
after day is done, post tEntry data to the perm table.

repeat the next day.

[ashraf_al_ani]

Dear ranman
The numbering should be automatic
I mean every record opens the field will increase by one then when another day starts the field will set to one again
I mean that every record has a number like restaurant system no matter if the number duplicate s in a diffrent day
Best regards

[burrina]

If I understand correctly you are going to calculate the date difference. Example Below. Of course you will then need to do what you want with the end result. If not Please explain further!

myDiff: datediff("d", [thisDate], [thatDate])

HTH

[June7]

Generating custom identifier is a common topic. Search forum. Start with

https://www.accessforums.net/forms/a...ing-23329.html

https://www.accessforums.net/access/...ers-21361.html

[ashraf_al_ani]

Hi all
pls i will re explain my problem again

my project is access project front end access and back end sql server

i have a field (num1) type integer in a table called (invoice)
there is another field called inv_date type datetime in the same table

i want num1 will be incremented by 1 when every record insert in the table in the same day

num1 inv_date
1 20/2/2014
2 20/2/2014
3 20/2/2014
4 20/2/2014

but when it becomes 21/2/2014 (next day)
the num1 will starts again from 1 automatically and so on

so please any ideas

best regards

[June7]

I gave you ideas in the referenced links. I think one suggests something like:

Nz(DMax("num1", "invoice", "inv_date=#" & Date() & "#"),0) + 1

Try putting that in the DefaultValue property of textbox bound to num1.

Why do you need this sequence number? Could calculate the sequence on a report. Textbox on report has a RunningSum property. It can be used to calculate sequence number.