Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DCOUNT Year to Date Summary 1

Status
Not open for further replies.

MrWilly

MIS
Nov 9, 2001
36
US
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'") And ("[Date_Time]","tblIssues","[Date_Time] Between Format('1/1/' & Year(Now),'m/d/yyyy') And Now")

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
 
=DCount("Standard_Issues","tblIssues","Standard_Issues ='Amendment Questions'" And "[Date_Time] Between '1/1/' & Year(Now())And Now()")

This should do it for you.

Paul

 
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?



 
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()")
 
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' ")

Mike
 
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# ")

Paul
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top