Looking up a distinct value

[yhgtbfk]

I want to count the number of distinct dates in a table. Ie - So I know there were 80 requests over 5 days.

The syntax I imagine is something like:

myText = Dlookup("count(DISTINCT myDate)", "myTable")

Can somebody please help.

Thank you in advance

 

[skea]

You need to create a recordset, loop through it, then count the Records.

 

[Dwight]

You can use a select distinct query to count the number of unique dates.

Example:

Select Distinct Count(YourDateField) AS CountOfYourDateField
FROM YourTable

So set up a regular query with Count in the total field and then in the query builder, go to SQL view and type in the word Distinct into your query's syntax.

Dwight

 

[john471]

I want to count the number of distinct dates in a table. Ie - So I know there were 80 requests over 5 days.

Your two statements seem contradictory to me. Someone else has addressed the first part; the below addresses the second part...

Use a Totals Query. Group By YourDateField and Count RecordIds.

SELECT myTable.MyDate, Count(myTable.MyRecordID) AS CountOfMyRecordID
FROM myTable
GROUP BY myTable.MyDate;