Web Wiz Forums Modifications - most acitve users

Print Page | Close Window

most acitve users

Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums Modifications
Forum Description: Mod's and Add-on's for Web Wiz Forums.
URL: https://forums.webwiz.net/forum_posts.asp?TID=25855
Printed Date: 30 March 2026 at 3:55am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: most acitve users

Posted By: leiti
Subject: most acitve users
Date Posted: 18 June 2008 at 7:22am

Hello,

i am searching for two sql scripts -> both for a Access DB:

first one (the simple one):

all user with their number of posts:

SELECT A.Username, COUNT(T.Author_ID) FROM tblAuthor A, tblThread T
WHERE A.Author_ID = T.Author_ID
GROUP BY A.Username
ORDER BY COUNT(T.Author_ID) DESC

second one:

all user with their number of posts between a date:

SELECT A.Username, COUNT(T.Author_ID) FROM tblAuthor A, tblThread T
WHERE A.Author_ID = T.Author_ID
AND T.Message_Date BETWEEN #01/06/2005# AND #30/06/2005#
GROUP BY A.Username
ORDER BY COUNT(T.Author_ID) DESC

The first script is working fine - but the second one wont bring the correct result. Can anyone take a view about it?

Replies:

Posted By: Jono
Date Posted: 18 June 2008 at 9:48am

I'm not an Access expert, but it may be that access is looking for a US format date (see: http://www.webmasterworld.com/databases_sql_mysql/3480321.htm - http://www.webmasterworld.com/databases_sql_mysql/3480321.htm ).

Does Access support ISO dates? (i.e. yyyy-mm-dd) If so, that would ensure there is no confusion.

When you say "correct results" does it return any records? If so, what are the values of "Message_Date" (you'll need to add this to the select statement)?

Posted By: leiti
Date Posted: 18 June 2008 at 10:51am

Thanks you are right - that was the solution ;-)

Posted By: WebWiz-Bruce
Date Posted: 18 June 2008 at 11:09am

I tend to use ISO dates for all database queries now, it also helps prevent issues between different date settings on servers.

The only database server I have come across that does understand the ISO yyyy-mm-dd date is SQL Server 2000 were you need to remove the dashes eg yyyymmdd, although Microsoft fixed this with SQL Server 2005.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting