CountOf for Date Fields in Query

Topic

New Reply

I have a query where I need to know the number of particular “Open” and “Overdue” records based on Date fields. The field “CloseDate” is being measured against the “DueDate”. I don’t need to know the number of records where the “CloseDate” is completed. I do however need to know the number of records that do not have an assigned “Close Date” that is [DueDate]for the OVERDUE category.

I can return all the records when I set the Query Total field to “GroupBy”, however, I need to number of records, not the actual records themselves. When I set the Total field to “Count” I get a long drawn out expression error.

Here is the acutal criteria I am setting:

Is Null AND <=[DueDate]

I am using the Is Null to only look for not completed fields.

Thanks for the assistance.

Reply | Quote

Replies

It is difficult for me to understand exactly what you have placed in the query grid.

On the Totals row, use Is Null in the criteria row for Close Date and set the Totals row to Where for that field. Use <=[Due Date] in a second criteria row for Close Date.You seem to be using AND instead of OR and a record cannot meet both criteria. Make sure you are counting on a field that is present in every record, like the key. Also, turn off the show box for the Close Date field.

If this doesn't help, please define what is in the grid more clearly and give us the error message.

Reply | Quote

I have the OPEN issues resolved by subtracting the [CountOfCloseDate] from the total # of records. This provides me with the total number of records still open.

The other issue of the OVERDUE field is still providing me some fits, though I did realize this morning that I was comparing the wrong fields. In stead of comparing the [CloseDate] to the [DueDate], (which can only work once the CloseDate is satisfied) I need to compare the [DueDate] to Todays date. If Todays’ date is greater or past the [DueDate] then I know that it is Overdue. Keep in mind though that I want the # of these instances, not the actual date returns. This query is feeding a summary form, kind of a high level overview. Here is what I added to my Query.

First I had to add the [DueDate] field, and set the Totals column to “Count”. This gave me the total number of records which established Due Dates, which equals the total number of records. All records require a due date or they cannot be completed at the point of generation. Here is where I am having problems:

Field: Overdue: Count(Date()>[DueDate])
Total: Expression

If I set the Total to count, then I get a long drawn out Expression error. Leaving it set to Expression returns the record count, but it equals the same # as the [DueDate] count, even though I have purposely set some of the Due Dates into June and July.

Any thoughts on my Expression?

Reply | Quote

It seems as if you trying to do this in one query and that may be part of the problem. I believe you need two.

I think you can get the Open records more easily in one query by merely counting the number of records with an Is Null Close Date. Count these records by the key.

For the overdue records, you need a seperate query in which you should count the number of records by the key (even though the Due Date is required, don’t use count on it right now) and use the Where expression <= Date() in the criteria row for Due Date.

You should have two queries, each with two fields in the grid. Try it that way and see if it resolves the error. Then, if you need to do more, you can build from that.

Reply | Quote

Hey that worked Tom. I actually didn’t need to use the WHERE expression once I separated what I was trying to get accomplished into 3 queries. I was pulling several “count” record fields, and once I separated it into 2 “base” queries and then joined the two together into the main query feeding my results form it works great.

Thanks again for the tip!

Reply | Quote