Hi,
I'm brand new to these online help sites, but I'm trying to create a usable database and I keep getting stuck. I'm not very experienced with Access code, macros, or anything beyond basic. My problem right now is I have a database ("Referrals") with clients that may or may not have a case number. I wish to count those clients with a case number for my report, but also to filter them to only the current month. I've tried using the following in the control source line of the property box on my unbound text box: =DCount("Case Number","Referrals","Date of Referral=Month(Now())") but all it says is "error" when I view the report. "Case Number" and "Date of Referral" are both fields in my database.

Thank you.

I would avoid using spaces in the names of any object or field.


Since it appear you have spaces in the name of a field, you must wrapp the filed name with [ and ]

Try:


=DCount("[Case Number]","Referrals","[Date of Referral]=Month(Date())")

This assumes that the field [Date of Referral] is a numeric data type.

Note: Now() includes both date and time. Date() only has the date, no time.

YAY!!! Thank you so much!

Any thoughts on how to do similar, counting those in the last calendar month? My goal would be something similar to Month(Date()) where the program figures out the dates within the time frame itself rather than having to enter actual beginning and end times. It seems I can get the past 30 days using Datedif, but those using this database may search on any day in the month and need to pull up stats on the previous calendar month, not just 30-day intervals.

These may help:

Displaying specific dates (http://www.hitechcoach.com/index.php?option=com_content&task=view&id=19&Itemid=9)

and

Report Dialog Examples (http://www.hitechcoach.com/index.php?option=com_docman&task=doc_details&gid=9&Itemid=28)

Thank you! :)

Okay, I've got the DCount to work perfectly, except that I want it to sort by another field. I am currently counting all records that are deemed "WHI" and occur within the current month - this is perfect. However, I also want the report to be sorted by the staff that worked with the client. I have the report based on a query that is set to Group By (though nothing in criterira and just "ascending" in sort), and I've set the report to group by "Outreach Staff". The report shows the different staff, but the same numbers for both - that being all clients not filtered by staff. How can I get seperate numbers for each staff while still being on the same report? Here is my control souce as it is now:

=DCount("Name","[Emb Reporting]","[Date of Referral] Between DateSerial(Year(Date()), Month(Date()), 1) and DateSerial(Year(Date()), Month(Date()) + 1, 0)")

Thank you for any help!

It appears that your Dcount() criteria is the same for all staff members.

You will need something like:

=DCount("*","[Emb Reporting]"," "[Name]" = " & Me.txtName & "[Date of Referral] Between DateSerial(Year(Date()), Month(Date()), 1) and DateSerial(Year(Date()), Month(Date()) + 1, 0)")


Note; Name is a property of controls an d should not be used as the name aof a field. It will only cause you headaches because Aces will get confused.


If it were me, I would do the counting in the query or with the report.

NOTE: If you are going to use grouping/sorting in the report, it will override any soring in the query. So this means that sorting is the query is really wasted processing since the report will have to resort the data again.

I appreciate your assistance, though I feel I need to take a course on how to speak "Access". I tried the code you listed and it says the syntax is wrong. I don't understand what & Me.txtName & really means, so it's hard to know where the error is. I think I did something wrong with how I have the "Outreach Staff" connected to everything else, thus I'm afraid I don't know enough to even ask an intelligent question to figure out where to go from here. I appreciate your help, and if I can sort out my mess, I'll probably be back. Thank you.