Results 1 to 6 of 6
  1. #1
    Coffee is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Location
    Australia
    Posts
    31

    Date/Time Field Query...


    Hello i have been search for many days and cannot seem to find a answer to my question so i thought i would seek assistance from this helpful community ;p

    I have a database that stores date and time in the same column. the field is of type Date/Time and cannot be changed. i would like to search between 2 dates and a specific time between those two dates in SQL.

    is this possible? the date is stored in the field as "2/05/2011 2:34:22 PM". i can search between 2 dates fine....but when every i try to question anything about the time it is like it cannot access the time value located in the column ;/

    Text36 = start date
    Text38 = finish date
    Text9 = start time
    Text11 = finish time

    Code:

    SELECT DISTINCT Historical_Data.MessageNo, Historical_Data.DateTime, Historical_Data.UserName, Historical_Data.FCS, Historical_Data.KKSCode, Historical_Data.Message

    FROM Historical_Data

    WHERE (((Historical_Data.DateTime) (BETWEEN Forms!Main_Form!Text36 AND Forms!Main_Form!Text38) AND (BETWEEN Forms!Main_Form!Text9 AND Forms!Main_Form!Text11)));

  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
    Let's clarify. Do you want records between the combination of the start date & start time through the combination of the end date & end time, or only records that fall between the start and end times during the date period? In other words, if your start/end dates are the 1st and 3rd and times are 10am and 11am, do all records on the 2nd get returned, or only those between 10 and 11am?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Coffee is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Location
    Australia
    Posts
    31
    hey,

    i would like it like this:

    User enters start date 1/1/2000 and end date 10/1/2000 and the time period start 10am and end time 11am.

    so it would return all the results that occurred on the 1st ,2nd ,3rd ,4th ,5th ,6th,7th,8th,9th and 10th, but only between the time of 10am and 11am

    eg.

    '2/1/2000 10:22:22 AM' would be returned, but '2/1/2000 11:56:00 AM' would not becasue it doesn't fall between the correct time.

  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
    Untested, but try

    WHERE DateValue(Historical_Data.DateTime) BETWEEN Forms!Main_Form!Text36 AND Forms!Main_Form!Text38 AND TimeValue(Historical_Data.DateTime) BETWEEN Forms!Main_Form!Text9 AND Forms!Main_Form!Text11
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Coffee is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Location
    Australia
    Posts
    31
    This is working perfectly at the moment!! now to add it to my larger SQL script and hope it works.

    Thank you very much!!! You have made my day =)

  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
    Happy to help, and welcome to the site!
    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: 7
    Last Post: 05-31-2011, 11:51 AM
  2. Access 2007 Date/Time Field
    By cdh in forum Access
    Replies: 3
    Last Post: 05-12-2011, 07:11 AM
  3. Access Subquery Max Date/Time field
    By ruci1225 in forum Access
    Replies: 1
    Last Post: 04-08-2011, 07:33 AM
  4. Date/Time entry field on a form
    By Ted C in forum Forms
    Replies: 1
    Last Post: 06-22-2010, 12:28 PM
  5. date/time field
    By James890 in forum Database Design
    Replies: 1
    Last Post: 04-18-2007, 07:51 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