asked on

GroupBy query with todays date

Access 2010:

SELECT Count(dbo_Desc_Search_Log.ProcRun) AS CountOfProcRun, dbo_Desc_Search_Log.QueryDate
FROM dbo_Desc_Search_Log
GROUP BY dbo_Search_Log.ProcRun, dbo_Desc_Search_Log.QueryDate
HAVING (((dbo_Desc_Search_Log.ProcRun)="1"));

I need to run this same query but only include records from todays date from the field.

dbo_Desc_Search_Log.QueryDate

The format for this field is like this:
7/21/2014 8:35:35 AM

Just need with todays date(everyday)

Thanks
fordraiders

ASKER CERTIFIED SOLUTION

Helen Feddema

membership

This solution is only available to members.

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

Start Free Trial

Fordraiders

ASKER

i have tried =date()

CountOfProcRun      QueryDate
1      7/21/2014 8:57:15 AM
1      7/21/2014 8:56:59 AM
1      7/21/2014 8:56:34 AM
1      7/21/2014 8:56:25 AM
1      7/21/2014 8:56:24 AM
1      7/21/2014 8:56:21 AM
1      7/21/2014 8:56:21 AM
1      7/21/2014 8:56:18 AM

Just trying to get everything starting with todays date... without say explicitly 7/21/2014...the time does not matter.

Helen Feddema

It looks as if you are getting the results you want -- all those records have today's date. Is there still a problem? If so, what is the problem?

Fordraiders

ASKER

The result i'm looking for is a count

CountOfProcRun
8

Helen Feddema

Make a calculated field like this: Format(QueryDate, "m/d/yyyy"), and do a count on that. This will eliminate the times that are causing separate records. You may have to tweak the query to make sure that other fields don't cause extra records. It would help if you could post the database, or a cut-down version of it.

Hamed Nasr

Try:

SELECT Count(dbo_Desc_Search_Log.ProcRun) AS CountOfProcRun, 
              FORMAT(dbo_Desc_Search_Log.QueryDate,"mm/dd/yyyy")
 FROM dbo_Desc_Search_Log
 GROUP BY dbo_Search_Log.ProcRun, FORMAT(dbo_Desc_Search_Log.QueryDate,"mm/dd/yyyy")
 HAVING (((dbo_Desc_Search_Log.ProcRun)="1"));

Open in new window

Fordraiders

ASKER

ok but i need it specifically for today's date..
in this case 7/21/2014

tomorrow
7/21/2014

Fordraiders

ASKER

hnasr, I will check that query..Thanks

Fordraiders

ASKER

using date function, just ended up nesting 2 queries.