Dcount since last sunday (1 Viewer)

[Leyton01]

I am trying to get a running total of documents we process during the week, displayed in an unbound text box with a dcount as the value.

I have managed to get the daily count using:
=DCount("*","[tblDocumentLog]","Int([DateCreated])= #" & Date() & "#")

But I am struggling with the correct syntax to count all documents that have been created since the last Sunday.

I am using Australian dates (so same as UK) and the database is never used on a Sunday, so the count on Sunday does not have to be correct (ie doen't matter if it is a single day count or 7 days count).

Bonus round: total since the first of the month.

Thanks!

 

[pbaldy]

Last Sunday:

?date() - Weekday(date(), 1) + 1
8/26/2018

First day of the month:

?DateSerial(Year(date()), Month(date()), 1)
8/1/2018

 

[Minty]

This returns the Monday of the current date

Date()- Weekday(Date(), vbMonday) + 1

So

=DCount("*","[tblDocumentLog]","Int([DateCreated])>= #" & Format( Date()- Weekday(Date(), vbMonday) + 1, "yyyy-mm-dd") & "#")

Should do the trick For part one.

Dateserial(year(date()), month(date()),1)

Returns the beginning of the current month. I'll let you have a play

Edit - Damn that Baldy's faster typing skills....

 

[Leyton01]

Thank you both for your help - a combination worked.


It errored out with vbMonday so had to use "1", also it did not like Format in the weekly expression:

DCount("*","[tblDocumentLog]","Int([DateCreated])>= #" & (Date()-Weekday(Date(),1)+1) & "#")

Opposite to this, the monthly would only work with the format code??

DCount("*","[tblDocumentLog]","Int([DateCreated])>= #" & Format(DateSerial(Year(Date()),Month(Date()),1),'yyyy-mm-dd') & "#")

Cheers