Count Records for this week, this month, this quarter, this year

[Silvio]

Hello, I am using the code--
=DCount("[ClaimID]","tblClaims","DateValue([DateCreated])=Date()") --to count
the number of record created today. I would like to use a similar code to
count the number of record created:
1) This week
2) This Month
3) This Quarter
4) This Year

When I say “thisâ€, I mean the current week, month, Quarter and Year based on
NOW. For example, if I have 5 record for December 2009 and 6 records for
December 2008, since we are in 2009 the total for “This Month†will be 5 (not
5 + 6 = 11). Also, my record CreatedDate has a date/time value.

Thank you,
Silvio

 

[Allen Browne]

This month would be like this:
=DCount("*", "tblClaims", "[DateCreated] >= " &
Format(DateSerial(Year(Date()), Month(Date()), 1), "\#mm\/dd\/yyyy\#")

Notes:
a) Assumes DateCreated is a date/time field.
b) Don't change the format string even if your local date format is not
American.
c) DateSerial() accepts 3 numbers: year, month, and day.
d) For this year, substitute 1 for the middle number.
e) Use DatePart() to get the quarter. Subtract 1, multiply by 3, and add 1
to get the month starting this quarter.

 

[Silvio]

Hi Allen, thank again for your help. Do you have an example I for item e?

This month would be like this:
=DCount("*", "tblClaims", "[DateCreated] >= " &
Format(DateSerial(Year(Date()), Month(Date()), 1), "\#mm\/dd\/yyyy\#")

Notes:
a) Assumes DateCreated is a date/time field.
b) Don't change the format string even if your local date format is not
American.
c) DateSerial() accepts 3 numbers: year, month, and day.
d) For this year, substitute 1 for the middle number.
e) Use DatePart() to get the quarter. Subtract 1, multiply by 3, and add 1
to get the month starting this quarter.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello, I am using the code--
=DCount("[ClaimID]","tblClaims","DateValue([DateCreated])=Date()") --to
count
the number of record created today. I would like to use a similar code to
count the number of record created:
1) This week
2) This Month
3) This Quarter
4) This Year

When I say “thisâ€, I mean the current week, month, Quarter and Year based
on
NOW. For example, if I have 5 record for December 2009 and 6 records for
December 2008, since we are in 2009 the total for “This Month†will be 5
(not
5 + 6 = 11). Also, my record CreatedDate has a date/time value.

Thank you,
Silvio

.

 

[Allen Browne]

Current quarter starts:
DateSerial(Year(Date()), 3 * (DatePart("q",Date()) - 1) + 1, 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen, thank again for your help. Do you have an example I for item e?

This month would be like this:
=DCount("*", "tblClaims", "[DateCreated] >= " &
Format(DateSerial(Year(Date()), Month(Date()), 1), "\#mm\/dd\/yyyy\#")

Notes:
a) Assumes DateCreated is a date/time field.
b) Don't change the format string even if your local date format is not
American.
c) DateSerial() accepts 3 numbers: year, month, and day.
d) For this year, substitute 1 for the middle number.
e) Use DatePart() to get the quarter. Subtract 1, multiply by 3, and add
1
to get the month starting this quarter.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello, I am using the code--
=DCount("[ClaimID]","tblClaims","DateValue([DateCreated])=Date()") --to
count
the number of record created today. I would like to use a similar code
to
count the number of record created:
1) This week
2) This Month
3) This Quarter
4) This Year

When I say “thisâ€, I mean the current week, month, Quarter and Year
based
on
NOW. For example, if I have 5 record for December 2009 and 6 records
for
December 2008, since we are in 2009 the total for “This Month†will be
5
(not
5 + 6 = 11). Also, my record CreatedDate has a date/time value.

Thank you,
Silvio

.

 

[Silvio]

Hi Allen, it appears that the code counts everything. Probably I am doing
something wrong: =DCount("*","tblClaims","[DateCreated] >= " &
DateSerial(Year(Date()),3*(DatePart("q",Date())-1)+1,1))

Any idea?

Current quarter starts:
DateSerial(Year(Date()), 3 * (DatePart("q",Date()) - 1) + 1, 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen, thank again for your help. Do you have an example I for item e?

This month would be like this:
=DCount("*", "tblClaims", "[DateCreated] >= " &
Format(DateSerial(Year(Date()), Month(Date()), 1), "\#mm\/dd\/yyyy\#")

Notes:
a) Assumes DateCreated is a date/time field.
b) Don't change the format string even if your local date format is not
American.
c) DateSerial() accepts 3 numbers: year, month, and day.
d) For this year, substitute 1 for the middle number.
e) Use DatePart() to get the quarter. Subtract 1, multiply by 3, and add
1
to get the month starting this quarter.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Hello, I am using the code--
=DCount("[ClaimID]","tblClaims","DateValue([DateCreated])=Date()") --to
count
the number of record created today. I would like to use a similar code
to
count the number of record created:
1) This week
2) This Month
3) This Quarter
4) This Year

When I say “thisâ€, I mean the current week, month, Quarter and Year
based
on
NOW. For example, if I have 5 record for December 2009 and 6 records
for
December 2008, since we are in 2009 the total for “This Month†will be
5
(not
5 + 6 = 11). Also, my record CreatedDate has a date/time value.

Thank you,
Silvio

.

.

 

[Silvio]

The date format was the problem. For everyone's benefit here is the working
code:

=DCount("*","tblClaims","[DateCreated] >= " &
Format(DateSerial(Year(Date()),3*(DatePart("q",Date())-1)+1,1),"\#mm\/dd\/yyyy\#"))

Current quarter starts:
DateSerial(Year(Date()), 3 * (DatePart("q",Date()) - 1) + 1, 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen, thank again for your help. Do you have an example I for item e?

This month would be like this:
=DCount("*", "tblClaims", "[DateCreated] >= " &
Format(DateSerial(Year(Date()), Month(Date()), 1), "\#mm\/dd\/yyyy\#")

Notes:
a) Assumes DateCreated is a date/time field.
b) Don't change the format string even if your local date format is not
American.
c) DateSerial() accepts 3 numbers: year, month, and day.
d) For this year, substitute 1 for the middle number.
e) Use DatePart() to get the quarter. Subtract 1, multiply by 3, and add
1
to get the month starting this quarter.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Hello, I am using the code--
=DCount("[ClaimID]","tblClaims","DateValue([DateCreated])=Date()") --to
count
the number of record created today. I would like to use a similar code
to
count the number of record created:
1) This week
2) This Month
3) This Quarter
4) This Year

When I say “thisâ€, I mean the current week, month, Quarter and Year
based
on
NOW. For example, if I have 5 record for December 2009 and 6 records
for
December 2008, since we are in 2009 the total for “This Month†will be
5
(not
5 + 6 = 11). Also, my record CreatedDate has a date/time value.

Thank you,
Silvio

.

.