using DCount instead of a recordset

[crowegreg]

I've created a recordset to determine a recordcount. Here is my SQL string:
SELECT PC_Log.ReportDate FROM PC_Log
GROUP BY PC_Log.ReportDate, Left([Request Number],4)
HAVING (((Left([Request Number], 4)) = 'FY14'))

Is it possible to use DCount instead? I can't figure out how to use the "GROUP" within the DCount statement.



Thank you in advance!!

[pbaldy]

Presuming you're counting the dates, the criteria should handle it.

[crowegreg]

I can't figure out the syntax.

Here's what my data looks like:

Request Number,ReportDate
FY14-001, 07/12/13
FY14-002, 07/12/13
FY14-003, 07/26/13
FY14-004, 07/26/13
FY14-005, 07/26/13
FY14-006,08/09/13

Using DCount, it should be 3.

[pbaldy]

What have you got so far? I assume you also have a date criteria to get 3.

[crowegreg]

Here's what I have:
intRecordCount = DCount("[ReportDate]", "PC_Log", "Left([Request Number], 4) = '" & strFYYear & "'")

strFYYear = "FY14"

With the data above, I'm returning 6. I need 3.

[pbaldy]

Well, there are 6 records with that criteria, so which 3 are you expecting it to count?

[pbaldy]

Oh, I get it, you want that there are 3 distinct dates? I don't think you can get that directly from the table, but you could apply a DCount() to a query with that SQL.

[crowegreg]

I'm trying to retrieve distinct dates.
07/12/13
07/26/13
08/09/13

count of 3

[crowegreg]

That's what I didn't know. When this is executed, which takes less time, creating a recordset, or creating a querydef, then using DCount with the query?