Results 1 to 4 of 4
  1. #1
    ostroms1 is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    2

    Between dates query question

    Greetings,



    I have a db where Im pulling data with Between date1 and date2.

    example:

    date1 = 5/17/10 6:00 AM
    date2 = 7/16/10 3:30 PM

    the query pulls everything like it should, but I need to exclude anything that is "after hours".

    The data that Im looking for would be like the following:

    5/17/10 6:00 AM to 3:30 PM
    5/18/10 6:00 AM to 3:30 PM
    .
    .
    7/16/10 6:00 AM to 3:30 PM

    Is there a way to do this without having to querey each date with the specific times?

  2. #2
    esglover is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    3
    Is there a chance that you could split the date field in the database so that you have one column for Date and one column for Time? That would make the problem much easier.

    You could do a query that searches for Date between 5/17/10 and 7/16/10 and also Time between 6:00 AM and 3:30 PM.

  3. #3
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Query with DatePart() like so:

    Code:
    SELECT * FROM MyTable
    WHERE (((TimeValue([DateField])) Between "06:00:00" And "15:30:00") AND ((DateValue([DateField])) Between [Date1] And [Date2]) AND (Not (DatePart("w",[DateField]))=1 And Not (DatePart("w",[DateField]))=7));
    The above Query returns all the Records from MyTable that are between [Date1] and [Date2] as long as it is ALSO:
    Between 6:00 AM and 3:30 PM, AND
    Between Monday and Friday

  4. #4
    ostroms1 is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    2
    That is exactly what I was looking for. Thank you for assist!

    Spencer

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

Similar Threads

  1. Query Question
    By starhannes in forum Queries
    Replies: 13
    Last Post: 05-06-2010, 04:05 PM
  2. Replies: 3
    Last Post: 04-26-2010, 11:38 AM
  3. Sorting in a Query (Dates)
    By JohnS in forum Queries
    Replies: 2
    Last Post: 10-23-2009, 03:51 AM
  4. Update query for 12/30/1899 dates
    By P5C768 in forum Queries
    Replies: 8
    Last Post: 09-25-2009, 09:16 AM
  5. Matching Query Dates
    By rgwynne in forum Queries
    Replies: 1
    Last Post: 08-13-2009, 05:23 AM

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