asked on

How to query within a specific date and time range in Access Database

Hello,
A continuation of question

https://www.experts-exchange.com/questions/21822615/How-to-query-within-a-specific-date-range-in-Access-Database.html

I have the following query...

SELECT working.t_mode AS Mode, Count(working.t_mode) AS [Number by Mode], Count(t_mode)/(SELECT COUNT(*) FROM try) AS [Mode Percent]
FROM working
WHERE Cdate(Format(working.t_dttm,'mm/dd/yyyy')) BETWEEN
#3/1/2006# and #3/3/2006#
GROUP BY working.t_mode;

Note: The date field I will be using is a date and time stamp...so will have to figure out how to deal with it with the time in it or how to loose the tiem.
e.g. 2006-03-03 7:58:31 AM

1) How do I query within a time range associated to a date or perhaps not associated to a date...and
2) How do I count the records returned so that when I calculate the Mode Percent it is divided by the records returned (within the date/time range queried) versus the entire number of records in the table.

Thanks Groovymonkey

Rey Obrero (Capricorn1)

you can use TimeValue to get the time from a date field

TimeValue(#2006-03-03 7:58:31 AM#) will give you

7:58:31 AM

nico5038

You can also include the min and max time for the start and end date like:

SELECT working.t_mode AS Mode, Count(working.t_mode) AS [Number by Mode], Count(t_mode)/(SELECT COUNT(*) FROM try) AS [Mode Percent]
FROM working
WHERE working.t_dttm BETWEEN
#3/1/2006 00:00:00# and #3/3/2006 23:59:50#
GROUP BY working.t_mode;

Nic;o)

nico5038

Oops, overlooked part 2, for that you can use:

SELECT working.t_mode AS Mode, Count(working.t_mode) AS [Number by Mode], Count(working.t_mode)/(SELECT COUNT(*) FROM working) AS [Mode Percent]
FROM working
WHERE working.t_dttm BETWEEN
#3/1/2006 00:00:00# and #3/3/2006 23:59:50#
GROUP BY working.t_mode;

or the slower use of the Dcount function like:

SELECT working.t_mode AS Mode, Count(working.t_mode) AS [Number by Mode], Count(working.t_mode)/DCOUNT("*","working") AS [Mode Percent]
FROM working
WHERE working.t_dttm BETWEEN
#3/1/2006 00:00:00# and #3/3/2006 23:59:50#
GROUP BY working.t_mode;

Nic;o)

groovymonkey

ASKER

Hello,
I could be wrong but the code above will divide by the total number of records in the db...I only want it to divide by the number of the records that is returned by the specific date/time query.

ASKER CERTIFIED SOLUTION

nico5038

membership

This solution is only available to members.

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

Start Free Trial