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
adam.thwaites.remove@thisbit.mercedes.co.uk
Manchester, UK

Hi Adam,

Try this:

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

CW

"Adam Thwaites" wrote:

> 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
> adam.thwaites.remove@thisbit.mercedes.co.uk
> Manchester, UK

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

"Adam Thwaites" wrote:

> 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
> adam.thwaites.remove@thisbit.mercedes.co.uk
> Manchester, UK

Ken,

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

CW

"Ken Sheridan" wrote:

> 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
>
> "Adam Thwaites" wrote:
>
> > 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
> > adam.thwaites.remove@thisbit.mercedes.co.uk
> > Manchester, UK
>

Adam Thwaites wrote:
> 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)


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com