How can I query time frame from date time field in Access?

I have a database that tracks a  transaction.  I want to be able to write a query that shows when this transaction happens between the hours of 7:00 am and 9:30am each day. I also want to be able to see this week over week as a trend.  I have 3 available fields to use that are date/time related.  I have a "Date/Tm" field that shows "12/2/2014  7:17:12 AM". I have a "Date" field that shows "12/02/2014".  I also have a "Day of the Week" field that shows "Tuesday".  I also have fields for "Employees", "Locations", and "Transactions"

I am envisioning writing a query that could be exported and used in a pivot table to show the employee, the location, and the amount of transactions that occurred each week by day and within the specified time frame. I have attached an example of what I would like to see in the resulting query.

Thanks!
Dominator1025Asked:
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("12/2/2014  7:17:12 AM")

7:17:12 AM    < result
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
Rey Obrero (Capricorn1)Commented:
in your query use  timeValue([Date/Tm])
0
Rey Obrero (Capricorn1)Commented:
you can also use

Timeserial(Hour([Date/Tm]), Minute([Date/Tm]),second([Date/Tm]))
0
Nick67Commented:
You don't realize it yet, but how you are storing your data is screwed
I have 3 available fields to use that are date/time related.  I have a "Date/Tm" field that shows "12/2/2014  7:17:12 AM". I have a "Date" field that shows "12/02/2014".  I also have a "Day of the Week" field that shows "Tuesday".

It is very likely that they are all storing the very same thing.
Access stores DateTimes as a special kind of Double.
The decimal part is always positive and is the fraction of the day
The Integer part represents the day, with Day zero being 30-Dec-1899.

You can format the fields all you like -- but they STORE the same thing

SO

You can throw a calculated field into your query CDbl([Date/Tm]) - CLng([Date/Tm]) and for a criteria, you want that to be BETWEEN 7/24 AND 9.5/24

Then group it however you'd like and pivot it.
0
Gustav BrockCIOCommented:
You can use this query with Weekday to obtain a useful sort order:

Select
    Employees,
    Locations,
    [Day of the Week],
    TimeValue([Date/Tm]) As TransactionTime,
    Sum(Transactions) As TotalTransactions
From
    tblYourTable
Group By
    Employees,
    Locations,
    Weekday([Date/Tm]),
    [Day of the Week]

/gustav
0
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.