Results 1 to 14 of 14
  1. #1
    wolfm is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2013
    Posts
    93

    combining time ranges between two fields

    Hello: Not sure how to build this expression. I have 2 fields; [TimeofDelivery] and [ReturnTime] in a query running from a table [Truck Runs]!

    If either of the two times fall between <8 am and >4:30 pm, I want those records to show.

    I have tried the expression:


    Code:
    =IIf([Truck Runs]![TimeofDelivery] <#8:00:00 AM# Or >#4:30:00 PM# OR [Truck
    Runs]![ReturnTime] <#8:00:00 AM# Or >#4:30:00 PM#)
    I get the error "The expression you entered has a function containing the wrong number of arguments"

    Am I close, or way off?

    Thanks for any help!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Way off.

    You haven't supplied the True and False arguments of the IIf(), just the test. You have to repeat the field names for each test in an "OR". Try:

    =IIf([Truck Runs]![TimeofDelivery] <#8:00:00 AM# Or [Truck Runs]![TimeofDelivery] >#4:30:00 PM# OR [TruckRuns]![ReturnTime] <#8:00:00 AM# Or [TruckRuns]![ReturnTime] >#4:30:00 PM#, True, False)

    That works in a form/report textbox. If you want it in the criteria of a query, it would be a little different.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    wolfm is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2013
    Posts
    93
    Hi Paul:
    When I added this expression and ran the query, I got not records. I believe I have to add IsNull to the expression due to the fact that some of the [Truck Runs]![TimeofDelivery] and some of the [TruckRuns]![ReturnTime] field data is missing in some records (blank).
    Is it possible to add IsNull to this expression? Thank you!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What I posted would be in a form/report. How did you use it in a query (show the SQL)? You can use IsNull(), but Nz() may be more appropriate. Depends on what you want to do with them.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    wolfm is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2013
    Posts
    93
    SQL Version:
    Code:
    SELECT [Truck Runs].ID, [Truck Runs].TimeofDelivery, [Truck Runs].ReturnTime, [Truck Runs].StoreFieldForOtRuns
    FROM [Truck Runs]
    WHERE ((([Truck Runs].StoreFieldForOtRuns)=IIf([Truck Runs]![TimeofDelivery]<#12/30/1899 8:0:0# Or [Truck Runs]![TimeofDelivery]>#12/30/1899 16:30:0# Or [TruckRuns]![ReturnTime]<#12/30/1899 8:0:0# Or [TruckRuns]![ReturnTime]>#12/30/1899 16:30:0#,True,False)));
    Thanks.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I mentioned it would be different in a query. Try

    SELECT [Truck Runs].ID, [Truck Runs].TimeofDelivery, [Truck Runs].ReturnTime, [Truck Runs].StoreFieldForOtRuns
    FROM [Truck Runs]
    WHERE [TimeofDelivery]<#08:00:00 AM# Or [TimeofDelivery]>#16:30:00 PM# Or [ReturnTime]<#08:00:00 AM# Or [ReturnTime]>#16:30:00 PM#

    What are the data types of those 2 fields, and what's an example of what they contain?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    wolfm is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2013
    Posts
    93
    Hello:
    When I entered the criteria you gave me and ran the query, I got this error message:
    "The syntax of the subquery in this expression is incorrect. Check the subquery's syntax and enclose the subquery in parenthesis.
    TimeofDelivery is Date/Time, format Medium Time ie: 3:00 PM
    Return.Time is Date/Time, format Medium Time ie: 4:00 PM
    Thanks Again!

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    wolfm is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2013
    Posts
    93
    2018 Truck Runs - Copy.zipOK, let me know if this Zip file does not work.
    The query I'm working on is: 'Export4_Overtime_Truckruns'
    I want it to show only the truck Runs [ID] where the [TimeOfDelivery] is before 8:00 am, AND where [ReturnTime] is after 4:30 pm.
    Thanks again.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    There is no such query in the sample, and that seems to be a different requirement than we were talking about before. How about

    SELECT [Truck Runs].ID, [Truck Runs].TimeofDelivery, [Truck Runs].ReturnTime
    FROM [Truck Runs]
    WHERE [Truck Runs].[TimeofDelivery]<#8:00:00# AND [Truck Runs].[ReturnTime]>#16:30:00#
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    wolfm is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2013
    Posts
    93
    I get a syntax error:
    Click image for larger version. 

Name:	syntax.JPG 
Views:	5 
Size:	22.1 KB 
ID:	35253

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you paste that SQL into a new query? I ran it in your sample and it returned one record (I had to add an appropriate return time to a record).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    wolfm is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2013
    Posts
    93
    I created a new query and pasted in the SQL. It works great! Thank you!

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 1
    Last Post: 07-09-2014, 07:13 AM
  2. Replies: 14
    Last Post: 07-22-2013, 12:39 PM
  3. Combining two fields Help
    By Juan4412 in forum Queries
    Replies: 11
    Last Post: 09-30-2012, 05:40 PM
  4. two different time ranges
    By dylan_dog in forum Queries
    Replies: 1
    Last Post: 01-13-2012, 11:54 PM
  5. Combining fields
    By cotri in forum Forms
    Replies: 2
    Last Post: 01-18-2010, 12:06 PM

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