Results 1 to 4 of 4
  1. #1
    kj08 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    3

    Unhappy Query where there is no contact within a specific date range

    Hi all!



    About a week ago I thought I had a break through in running a query that would only pull up information for people who had no contact logged for a specified date range. The idea is that I want to know who I haven't contacted since the beginning of August. I tried running the query again today, but people that I have logged contact with in the last week are showing up as no contact in the specified date range. Any ideas what I should be doing different? Here is my SQL code that I'm using.

    Code:
    SELECT tblMasterList.EMPLID, [Full Demographics].C_EMAIL_ADDR, tblMasterList.FULL_NAME, [Full Demographics].PRI_LAST_NAME, [Full Demographics].PREF_FIRST_NAME, tblMasterList.FTIC_TERM, tblMasterList.STATUS, qryCommunications.COMMUNICATION_DT 
    FROM 
    (tblMasterList LEFT JOIN qryCommunications ON (tblMasterList.EMPLID = qryCommunications.EMPLID AND COMMUNICATION_DT >8/1/18 AND COMMUNICATION_DT < 9/20/18))
    INNER JOIN [Full Demographics] ON tblMasterList.EMPLID = [Full Demographics].EMPLID
    WHERE (((tblMasterList.FTIC_TERM)="FTIC-2168") AND ((tblMasterList.STATUS)="active") AND ((qryCommunications.COMMUNICATION_DT) Is Null))
    ORDER BY tblMasterList.FULL_NAME;
    Thanks for the help!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You have identified a query but you have not shown readers the related SQL.
    I suggest you show us a jpg of your tables and relationships --make sure the tables are expanded to show all fields.
    How are your Dates defined/dimmed?

    SQL formatted for readability:
    Code:
    SELECT tblMasterList.EMPLID
    	,[Full Demographics].C_EMAIL_ADDR
    	,tblMasterList.FULL_NAME
    	,[Full Demographics].PRI_LAST_NAME
    	,[Full Demographics].PREF_FIRST_NAME
    	,tblMasterList.FTIC_TERM
    	,tblMasterList.STATUS
    	,qryCommunications.COMMUNICATION_DT
    FROM (
    	tblMasterList LEFT JOIN qryCommunications ON (
    			tblMasterList.EMPLID = qryCommunications.EMPLID
    			AND COMMUNICATION_DT > 8 / 1 / 18
    			AND COMMUNICATION_DT < 9 / 20 / 18
    			)
    	)
    INNER JOIN [Full Demographics] ON tblMasterList.EMPLID = [Full Demographics].EMPLID
    WHERE (
    		((tblMasterList.FTIC_TERM) = "FTIC-2168")
    		AND ((tblMasterList.STATUS) = "active")
    		AND ((qryCommunications.COMMUNICATION_DT) IS NULL)
    		)
    ORDER BY tblMasterList.FULL_NAME;
    Good luck.

  3. #3
    kj08 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    3
    Hi, thanks for the response. The main issue is that I work with protected data and cannot give out much else. I will tell you the dates pull up as month/day/year the way that I put them into the query. I'm wondering if it's not working because the students are not contacted every day so they do have a blank for one of the dates in the range. I'm really new to this so I'm not 100% sure what I'm doing right or wrong.

    Thanks!

  4. #4
    kj08 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    3
    I actually figured it out! I just needed to add "#" before and after each date.

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

Similar Threads

  1. Replies: 3
    Last Post: 03-24-2017, 12:34 PM
  2. Replies: 7
    Last Post: 03-26-2015, 09:20 AM
  3. Replies: 7
    Last Post: 12-30-2013, 09:53 AM
  4. Selecting from specific Date and Time range
    By LindaRuble in forum Programming
    Replies: 1
    Last Post: 05-15-2013, 07:37 AM
  5. SELECT a specific DATE RANGE in Query
    By taimysho0 in forum Queries
    Replies: 28
    Last Post: 06-04-2012, 04:58 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