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

Hello,
A continuation of question

http://www.experts-exchange.com/Databases/MS_Access/Q_21822615.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

groovymonkeyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
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
0
nico5038Commented:
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)
0
nico5038Commented:
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)
0
groovymonkeyAuthor Commented:
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.
0
nico5038Commented:
Hmm, try:

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

Nic;o)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.