Autonumber counter that starts from 1 each day

[docxyz]

Hi, I made a simple access project and i want to make a counte for my visits,, this counter MUST starts from one each morning "every new day", I use autonumber field to give each visit its uniqe code, I'm ok with that, but i need additional counter that resets each new day,, i watched some videos for making the autonumber starts from different number like 1000 for example using Append query but i didnt figure out how to use this method for resetting counter everyday,, sorry for bad grammer

 

[llkhoutx]

Autonumbers can't repeat. Build a key with a real autonumber concantenated with a daily increment, easily generated with a function.

 

[CJ_London]

whenever you create a new record, use the following to complete the counter field.

dcount("*","myTable","theDate=" & Date())+1

You may have issues if you have more than one person inserting records at the same time, so the best time to apply the value is when the record is saved

 

[jdraw]

docxyz,

For your own reference you should read this article on autonumbers.

 

[docxyz]

whenever you create a new record, use the following to complete the counter field.

dcount("*","myTable","theDate=" & Date())+1

You may have issues if you have more than one person inserting records at the same time, so the best time to apply the value is when the record is saved

Thank you very much for your replies, dear CJ_London thank you for the code, I'm not expert in vba but this one seems a simple one.

 

[ButtonMoon]

Hi, I made a simple access project and i want to make a counte for my visits,, this counter MUST starts from one each morning "every new day", I use autonumber field to give each visit its uniqe code, I'm ok with that, but i need additional counter that resets each new day,, i watched some videos for making the autonumber starts from different number like 1000 for example using Append query but i didnt figure out how to use this method for resetting counter everyday,, sorry for bad grammer

At the start of each day, run the following ALTER TABLE command:

ALTER TABLE YourTable ALTER COLUMN ID AUTOINCREMENT (1,1);

Note that autonumber columns can and do generate duplicate values and you obviously will get duplicates if you do the above because the counter will start from 1 each day. I am assuming that your primary key in this table is something other than the autonumber column.

I don't really recommend this approach. Autonumbers impose certain limitations, not the least of which is that they can't be updated. Think carefully before using an autonumber in any way that is "meaningful" to your users.

 

[docxyz]

At the start of each day, run the following ALTER TABLE command:

ALTER TABLE YourTable ALTER COLUMN ID AUTOINCREMENT (1,1);

Note that autonumber columns can and do generate duplicate values and you obviously will get duplicates if you do the above because the counter will start from 1 each day. I am assuming that your primary key in this table is something other than the autonumber column.

I don't really recommend this approach. Autonumbers impose certain limitations, not the least of which is that they can't be updated. Think carefully before using an autonumber in any way that is "meaningful" to your users.

Hi ButtonMoon, thank you for your reply, I actually want to make counter field that reset each day rather than resetting the autonumber field, i use the latter as a primary key "visitID" and it cannot be reset, my question was about making a counter that counts encremently 1,2,3 and reset each new day but also doing that automatically rather than typing it manually, is that possible? Thank u again.