asked on

Select TOP 5 COUNT(*)

Hi all, I am having some difficulty with this statement, unfortunately it is executing but not producing a single EventID, which I need

Help?

month1=Month(NOW)&"/"&Day(NOW)&"/"&Year(NOW)
If Month(NOW)=1 Then
	month2=Month(NOW)+9&"/"&Day(NOW)&"/"&Year(NOW)-1
ElseIf Month(NOW)=2 Then
	month2=Month(NOW)+9&"/"&Day(NOW)&"/"&Year(NOW)-1
ElseIf Month(NOW)=3 Then
	month2=Month(NOW)+9&"/"&Day(NOW)&"/"&Year(NOW)-1
Else
	month2=Month(NOW)-3&"/"&Day(NOW)&"/"&Year(NOW)
End If

Set Popular5Events=oConn.Execute("SELECT TOP 5 COUNT(*),log_event FROM log WHERE log_date BETWEEN #"&month1&"# AND #"&month2&"# GROUP BY log_event HAVING COUNT(*)>0 ORDER BY COUNT(*) DESC")

Open in new window

Thanks all

Ryan Chong

what's your backend database you're using?

Graeme McGilvray

ASKER

Access 2003

cheers

Ryan Chong

in general, you can't do a plus/minus for the dates by adding +9 or minus -3

what's the criteria/logic for your date field?

Graeme McGilvray

ASKER

I use these dates (this specific format) for other Querys, it works fine

lets use to specific dates instead:

month1=4/25/2016
month2=1/25/2016

Ryan Chong

ok... so go to table: log and check if got records qualified within the log_date range between 4/25/2016 and 1/25/2016?

alternatively, rearrange your dates to put a smaller value in front in the between clause.

Graeme McGilvray

ASKER

Yes, the DB has dates within that range

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Graeme McGilvray

ASKER

Sorry I have no idea where to put this in Access 2003

I tested it in ASP, outputs the query of course