Count Function on Date Field 2

[diggerbob]

I have a report that I use that shows my natural gas usage per day. I also have to show how many days per month it was used. I have a count function set on the report but recently I have came across a problem. There will be days that we shut down and start back up on the same day, maybe even 3 times. I have to make separate entries for each start up using the same date. This causes my "days used" to come up as, i.e. 35 or 36 days for the month.
This is the way that I have my field formulated.
=Count([Limestone Gas Meter Reading Query]!Date)
Can anyone help on this!
Thanks, Bob

 

[traingamer]

One way is to use nested queries (tested).
QryDistinctDate

SELECT d1.GiftDate
FROM donations AS d1
GROUP BY d1.GiftDate;

Then, add an outer query

SELECT Count(qryDistinctDate.GiftDate) AS CountOfGiftDate
FROM qryDistinctDate;

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[dhookom]

You can create a subquery in you report's record source that counts unique dates. For instance in the Northwind.mdb you can create a column of unique order dates with a column/field expression like:

 DistinctOrderDates: (SELECT Count(*) FROM (SELECT DISTINCT OrderDate FROM ORDERS) )

There are 830 records in the ORDERS table but the expression returns 480.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[traingamer]

Much nicer.