DCOUNT Year to Date Summary 1

[MrWilly]

I am trying to break down records in an unbound report, by year.
The table (tblIssues) contains approx. 5 years of data.
The date field is Date_Time

Whenever I attempt to add a date filter to a DCOUNT statement, it returns a value of '-1'.
Without the date filter, the DCOUNT statemtent returns all records.

Can anyone debug the following statement and let me know why it's blowing up (it's for this year's records)?
=DCount "Standard_Issues","tblIssues","Standard_Issues='Amendment Questions'&quot And ("[Date_Time]","tblIssues","[Date_Time] Between Format('1/1/' & Year(Now),'m/d/yyyy') And Now&quot

Possibly even point me in the direction of how to properly filter a date. I've looked through all of the DCOUNT posts on Tek Tips, but am having quite a bit of trouble applying it to my report.


Any help is greatly appreciated.

Willy

 

[PaulBricker]

=DCount("Standard_Issues","tblIssues","Standard_Issues ='Amendment Questions'" And "[Date_Time] Between '1/1/' & Year(Now())And Now()&quot

This should do it for you.

Paul

 

[MrWilly]

Thanks Paul!

I've put this into an unbound text box on the report, and it's now returning the total amount of records from tblIssues.

Any ideas?

 

[PaulBricker]

Try this. I didn't look close at the Standard_Issues = 'Amendment Questions' part. This should do it. Post back if you have problems still.

=DCount("Standard_Issues","tblIssues","Standard_Issues ='Amendment Questions' And [Date_Time] Between '1/1/' & Year(Now())And Now()&quot

 

[MrWilly]

Worked like a charm! Thanks Paul!

One last thing, can you tell me if this is the correct format for determining previous year totals, eg 2002:

=DCount("Standard_Issues","tblIssues","Standard_Issues ='Amendment Questions' And [Date_Time] Between '1/1/2002' & '12/31/2002' &quot

Mike

 

[PaulBricker]

No, you would have to use the # sign to delimit your literal dates. Like this.

=DCount("Standard_Issues","tblIssues","Standard_Issues ='Amendment Questions' And [Date_Time] Between #1/1/2002# And #12/31/2002# &quot

Paul