Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2013

    Red face Unanswered: Data between 2 dates

    I am new to Access and I am currently putting together a simple database for my daughters small security company. I want to make a simple query to extract data between 2 dates. For example:
    I have a roster table with the following fields StaffName, SiteName, WorkDate, StartTime, EndTime, and Hoursworked. I want to be able to extract data from this table using dates i.e. between, 1/5/13 to 7/5/13. I have read several threads, but still cannot seem to crack it. I have created a Form that has the 2 date fields, a StaffName and a query button but it only seems to display the empty table. I am too embarrassed to give you the query string, because I know it is missing the display the required data. The data I want the query to display is StaffName, SiteName and Hoursworked fields in a table I have named RosterT
    If anyone can help it would be appreciated. Thank you in anticipation.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    select my, column, list from mytable
    where mydatecolumn between #2013/05/01# AND #2013/05/07#
    ...assuming you have entered the date or used the date() function to return on the date part of the current date and time

    if you used now() or have stored a time element then you need to alter the upper limit so that it returns all dates that are 7th may 2013. you have to do this as Access stores the fraction of the day alongside the day number so rerquesting less than 08th May will return all rows for the 7th.
    where mydatecolumn >= #2013/05/01# AND mydatecolumn < #2013/05/08#
    failing to do that measn that all rows which are set to midnight 07 may will be returned
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2013


    thanks for the prompt reply



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts