Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

Date Criteria in Access Query

In our retail store, employees are limited to the number of purchases they can make during our business day, which is from 4:00am - 3:59am (e.g., 3/1/11 4:00am - 3/2/11 3:59am is the March 1st business day) I have an access database that simply keeps track of the number of purchases made. I can do this easily if I only needed to keep track of purchases during the current calendar date, but I can't figure out how to do it for the current business day.

I have a query with the follwing fields: employee ID, timestamp, TransactionCount. To pull transactions for the current calendar day, I just enter "Date()" in the criteria under the Timestamp field. What I need is to see all transactions between today's date @ 4am and tomorrow's date at 3:59am.

Any ideas would be appreciated!
0
IslandIT
Asked:
IslandIT
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try this criteria

Between Date() + timeserial(4,0,0) and Dateadd("d",1,Date()) + timeserial(3,59,0)
0
 
Carl TawnSystems and Integration DeveloperCommented:
Probably not the most eleqant solution, but you coudl try:
WHERE [Timestamp] Between CDate(Date() & " 04:00") And CDate(Date()+1 & " 03:59")

Open in new window

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Where Format(PurchaseTime,"yyyy_mm_dd hh:nn")>=Format(Date(),"yyyy_mm_dd 04:00") AND Format(PurchaseTime,"yyyy_mm_dd hh:nn")<=Format(Date()+1,"yyyy_mm_dd 03:59")
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now