Results 1 to 4 of 4
  1. #1
    alancooke is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    2

    Checking if a date is in use

    Hi,

    My first post!

    I am building a small project management DB for myself and would appreciate advice on one aspect.

    I select engineers via a combo box in a form. I then enter the date they start and end. It occurred to me that a flaw in my thinking is that the engineer may be already working on another project so I cannot really select him for a new project using a date that is already booked.

    What would be the best way to check the start date I wish to enter against dates already booked against that engineer.

    So for example: Project 'A' he is booked from 1/1/2020 to 30/1/2020.


    I now create a new project 'B' and enter the date between 1/1/2020 and 30/1/2020 - I need some way of alerting myself that he is already booked for that month.

    Hope I made myself clear.

    TIA

    Alan

  2. #2
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    161
    In think you create a query with date filter between StartDate To EndDate . Query will give you the list of engineer free in between StartDate To EndDate.
    Set your Combo box to these list of free engineer.

  3. #3
    alancooke is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    2
    Hi, that's what I figured would be the way. I asked to see if perhaps there were other solutions.

    Thanks for your input!

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Here is an example query that will select all the engineers who are assigned to a project that overlap with given dates:

    Code:
    SELECT
      Engineers.EngineerID,
      Engineers.EngineerName
    FROM
      Engineers
    INNER JOIN
      Projects
    ON
      Engineers.EngineerID = Projects.EngineerID
    WHERE
      (Projects.ProjectStartDate<=[NEW PROJECT'S END DATE])
    AND
       (Projects.ProjectEndDate)>=[NEW PROJECT'S START DATE]);
    You can also use the list of "unavailable engineers" to select a list of available engineers during that time frame:
    Code:
    SELECT
      Engineers.EngineerID,
      Engineers.EngineerName
    FROM
      Engineers
    WHERE
      Engineers.EngineerID Not In (
        SELECT
          UnavailableEngineers.EngineerID
        FROM
          Engineers AS UnavailableEngineers
        INNER JOIN
          Projects
        ON
          UnavailableEngineers.EngineerID = Projects.EngineerID
        WHERE
          (((Projects.ProjectStartDate)<=[NEW PROJECT'S END DATE])
        AND
          ((Projects.ProjectEndDate)>=[NEW PROJECT'S START DATE]))
      );

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

Similar Threads

  1. Date checking for insurance expiration
    By St.Alphonzo in forum Macros
    Replies: 2
    Last Post: 07-27-2016, 11:54 AM
  2. Checking Date Range in Multiple Fields
    By mac322n in forum Queries
    Replies: 1
    Last Post: 05-11-2015, 07:00 PM
  3. Date checking code error
    By Huddle in forum Forms
    Replies: 1
    Last Post: 01-29-2015, 10:41 AM
  4. checking date when recordcount > 0
    By JeroenMioch in forum Programming
    Replies: 14
    Last Post: 03-05-2013, 12:39 PM
  5. Date error checking
    By oediaz in forum Programming
    Replies: 2
    Last Post: 03-26-2010, 12:08 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