Counter Reset Weekly

[Kay]

Hi
I have used a counter on my form that displays the total number of
record in my table. I put a label in my form and used it to show the
count. I have used the following code in the OnTimer() event:

Me!label59.Caption = DCount("*", "Current Jobs")


Has anyone any ideas how I can get the counter to reset to zero at the
start of every WEEK so that the counter only shows total records saved
during that week


Any help is much appreciated

 

[John Spencer]

What do you have in the table "Current Jobs" that tells you the record is in
the current week? Do you have a dateTime field that does this?

When you say current week, do you start the week on Sunday or some other day
of the week?

Assuming you have a datetime field and that you want to count all records
from Sunday forward, the following should give you an idea of how to do what
you want. You can lookup help on the DateAdd, WeekDay, and Date functions
in the VBA help.

DCount("*","Current Jobs","YourDateTimeField >= #" &
DateAdd("d",1-Weekday(Date()),Date()) & "#")