Dcount help - counting by month

[Adam Thwaites]

I give up on this one, can anyone help?

DCount("*", "tbl_MAIN", Format("[SaveDate]", "yyyymm") = Format(Date,
"yyyymm"))

I'm trying to count all records in tbl_Main that have a SaveDate in this
month. This formula above just gives 0, and any alteration just result in a
'list seperator or )' error. Any ideas?

Thanks!

 

[Cheese_whiz]

Hi Adam,

Try this:

DCount("[SaveDate]", "tblMain", "Month([SaveDate]) = Month(Date())")

CW

 

[Ken Sheridan]

It’s a little tricky getting it right because the arguments of the DCount
function are each a string expression, the format pattern argument of the
Format pattern is also a string expression, and the Format function returns a
string expression, so when building the whole expression contiguous pairs of
quotes have to be used to represent the literal quotes characters, like so:

DCount("*", "tbl_MAIN", "Format(SaveDate,""yyyymm"") = """ &
Format(Date,"yyyymm") & """")

Alternatively you can us CW's approach, but you need to call the Year
function as well as the Month function or rows from the same month in every
year represented in the table will be counted:

DCount("*", "tbl_MAIN", "Year(SaveDate) = " & Year(Date) & " And
Month(SaveDate) = " & Month(Date))

Each of the above is a single line of code of course; they might well be
split over two lines by your newsgroup reader.

Ken Sheridan
Stafford, England

 

[Cheese_whiz]

Ken,

I posted so fast I forgot about the years. Thanks for catching that!

CW

It’s a little tricky getting it right because the arguments of the DCount
function are each a string expression, the format pattern argument of the
Format pattern is also a string expression, and the Format function returns a
string expression, so when building the whole expression contiguous pairs of
quotes have to be used to represent the literal quotes characters, like so:

DCount("*", "tbl_MAIN", "Format(SaveDate,""yyyymm"") = """ &
Format(Date,"yyyymm") & """")

Alternatively you can us CW's approach, but you need to call the Year
function as well as the Month function or rows from the same month in every
year represented in the table will be counted:

DCount("*", "tbl_MAIN", "Year(SaveDate) = " & Year(Date) & " And
Month(SaveDate) = " & Month(Date))

Each of the above is a single line of code of course; they might well be
split over two lines by your newsgroup reader.

Ken Sheridan
Stafford, England

I give up on this one, can anyone help?

DCount("*", "tbl_MAIN", Format("[SaveDate]", "yyyymm") = Format(Date,
"yyyymm"))

I'm trying to count all records in tbl_Main that have a SaveDate in this
month. This formula above just gives 0, and any alteration just result in a
'list seperator or )' error. Any ideas?

Thanks!

--
Adam Thwaites
Access Database Designer
(e-mail address removed)
Manchester, UK

 

[Rick Brandt]

I give up on this one, can anyone help?

DCount("*", "tbl_MAIN", Format("[SaveDate]", "yyyymm") = Format(Date,
"yyyymm"))

I'm trying to count all records in tbl_Main that have a SaveDate in
this month. This formula above just gives 0, and any alteration just
result in a 'list seperator or )' error. Any ideas?

Thanks!

Criteria should be applied to fields, not expressions based on fields.

More efficient than other alternatives would be...

Dim whr as String
whr = "SaveDate >= DateSerial(Year(Date()), Month(Date()), 1) " & _
"AND SaveDate < DateSerial(Year(Date()), Month(Date()) + 1, 1)"

DCount("*", "tbl_MAIN", whr)