Results 1 to 3 of 3
  1. #1
    Zoober1 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    1

    How to use Expression Builder in a query to the show dates and times a transaction occurred

    Hello everyone,



    I'm new to access and this forum, so please take it easy on me. I am creating a database that tracks transactions (goods in exchange for money) that occur in our office. I have a table called 'TblTransactions' that stores: ID, CustomerName, CompanyType, CompanyName, Date, Time.

    Our office is open 24/7/365, so we use three shifts to provide this coverage - Day Shift (07:00 - 15:00), Swing Shift (15:00 - 23:00), and Midnight Shift (2300 - 0700).

    Part of what I've been asked to do is create a report that is printed at the end of each shift showing the transactions that occurred during that shift.

    I created three queries to drive each of these reports:
    1. QryDailyTransactionReport - DayShift
    2. QryDailyTransactionReport - SwingShift
    3. QryDailyTransactionReport - MidnightShift


    The TransactionDate field in each query is based on an unbound 'Date/Time' text box on the 'Switchboard' (aka home screen). It uses the following Criteria: [Forms]![Switchboard]![Date].

    I used the expression builder to create a field called 'Shift'. I used the following IIF statement and it works for every query except QryDailyTransactionReport - MidnightShift:


    • Shift: IIf(Format([Time],"Short Time") Between "07:00" And "14:59","Day",IIf(Format([Time],"Short Time") Between "15:00" And "22:59","Swing",IIf(Format([Time],"Short Time") Between "23:00" And "06:59","Midnight","Midnight")))


    The query only pulls transactions that occurred on the current date. Since the first hour of the Midnight Shift happens during the previous day, the data is not accurate. Is there a way to change this expression so that the last IIF statement includes transactions that occurred after 23:00 on the day before?

    Thank you in advance!

    -Zoober

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I thought you were doing great with field names until I came to Date, Time. These are reserved words and should not be used - http://allenbrowne.com/AppIssueBadWord.html

    Without seeing your problem query, I'd say you're making it more difficult to get what you want because you've separated Date and Time values. Access uses midnight where a time is not stored as part of the date value. This value is a double precision type number where left of the decimal represents the number of days, and to the right, the number of seconds elapsed since midnight December 31, 1899. You are probably trying to use the hours/minutes portion of a time value that has not been recorded for a given date in your table. It makes no sense to separate these values into two fields, since in order to use them with your required precision, you have to put them together as a value that represents a number like I just described. Again, since I can't see your query, there's an additional possible problem. If your query uses the BETWEEN operator, Access defaults the second date in this situation to midnight of the day before the specified date. In other words, if you say BETWEEN some date and 06/15/2017, you get no values that you'd expect at say, 2 minutes after midnight on the 15th. You have to use the DateAdd function to add the required number of minutes/seconds to the second date, or I believe, use <= and >= operators instead of BETWEEN.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-03-2016, 01:51 PM
  2. Replies: 7
    Last Post: 02-10-2016, 10:00 AM
  3. Query expression builder missing data
    By sangamc in forum Access
    Replies: 5
    Last Post: 09-16-2015, 01:46 PM
  4. Replies: 2
    Last Post: 11-20-2012, 03:21 AM
  5. expression builder: update query
    By JMac in forum Access
    Replies: 3
    Last Post: 05-04-2012, 03:19 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums