Results 1 to 3 of 3
  1. #1
    scschuck is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016

    Question on dates

    This is my first post, so hopefully I follow protocol. I have a database of roughly 8000 employees of the the course of 10 years. I know we can pull start dates, end dates no problem. My questions is this...How can I type in a date and know who is working for me or was working for me at any given date. IE...I want to know for July 4, 2001, who was with me...Or from May 15, 2016 through May 17, 2016, who was with us...

    I would appreciate any and all wisdom on this.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Ontario, Canada
    Compare the EndDate to your supplied date. If it's less than or equal to, they were there then. If you include Null, they are still there because there is no end date (or the entry was forgotten). So you could have separate query fields with the necessary calculation, or put EndDate <= [Enter a Date] on one criteria row and Is Null on the next criteria row for the same field. The words between those square brackets will provide a prompt for the date.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Ottawa, ON (area)
    Welcome to the forum.

    I'll start you off with the logic. For the first case, where you have only one test date (call it Date1), you want to find all employees who started before that date, and who finished after that date or are still with you:

    [StartDate] < [Date1] AND ( [EndDate] > [Date1] OR [EndDate] is Null )

    If you have a date range Date1 and Date2, it's only slightly different. You need employees who started somewhere in the date range, or who started before the first date and ended after the first date or are still with you:

    ( [StartDate] between [Date1] and [Date2]) OR ( [StartDate] < [Date1] AND ( [EndDate] > [Date1] OR [EndDate] is Null ) )

    Actually, you only need the second one, because the first scenario - with only Date1 - is a special case of the second scenario, but with Date2 = Date1.

    How you use that (i.e. syntax etc.) will depend on whether you are using a query or a form with SQL. Working with dates can be "tricky" to say the least.

    Post back if you need help with how to use the logic.

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

Similar Threads

  1. Replies: 1
    Last Post: 04-20-2015, 04:17 PM
  2. question|dates calculating
    By mikichi in forum Access
    Replies: 4
    Last Post: 12-05-2013, 09:31 AM
  3. Replies: 8
    Last Post: 12-02-2013, 03:46 PM
  4. ACCESS VB using Excel Dates Question
    By jscriptor09 in forum Programming
    Replies: 1
    Last Post: 10-11-2011, 07:42 PM
  5. Between dates query question
    By ostroms1 in forum Queries
    Replies: 3
    Last Post: 07-23-2010, 05:04 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