SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices WHERE UPS_Invoices.TrackingID NOT IN (SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"')

SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices WHERE NOT EXISTS.....

SELECT DISTINCT UPS_Invoices.TrackingID, UPS_Invoices.[transaction date] FROM UPS_Invoices WHERE UPS_Invoices.[transaction date] <= '"& myDate &"' AND UPS_Invoices.[transaction date] >= '"& myDateLess7 &"' ORDER BY trackingid

SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"'  ORDER BY trackingid

ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"'

<form method="POST" action="ups3.asp">
	<p><nobr>
<!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %><%
fp_sQry="SELECT DISTINCT invoicedate FROM ups_invoices order by invoicedate desc"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="newmas"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice=""
fp_sMenuValue="InvoiceDate"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<font face="Verdana">Please select a date to view.</font>   
<select NAME="Invoice_Date" SIZE="1">
<!--#include file="_fpclass/fpdbrgn1.inc"-->

<option><%=FP_FieldHTML(fp_rs,"InvoiceDate")%></option> 

<!--#include file="_fpclass/fpdbrgn2.inc"-->
</select>
</nobr><input type="submit" value="Submit" name="invoicedate"></p>
</form>

SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"'  ORDER BY TRACKINGID;

quote:

Given that, the SQL needed to simply get the TrackingIDs that exist in UPS_Invoices but not in AR_19InvoiceHistoryTracking would need to be something like:

SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices WHERE UPS_Tracking.InvoiceDate = '"& myDate &"' AND UPS_Invoices.TrackingID NOT IN (SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"')

quote:

I will never be able to apologize enough but I made a grave error.
I realized that I gave you incorrect information. Here is the correct info.

1 DSN
3 Tables:
UPS_Invoices w/ fields (TrackingID, invoicedate, Ref1, Ref2, Ref3)
ARN_InvHistoryHeader w/ fields (invoicenumber, invoicedate)
AR_19InvoiceHistoryTracking w/ fields (TrackingID, invoicenumber)

SELECT DISTINCT dbo_AR_19InvoiceHistoryTracking.TrackingID, dbo_ARN_InvHistoryHeader.InvoiceDate
FROM dbo_AR_19InvoiceHistoryTracking INNER JOIN dbo_ARN_InvHistoryHeader ON dbo_AR_19InvoiceHistoryTracking.InvoiceNumber = dbo_ARN_InvHistoryHeader.InvoiceNumber
WHERE (((dbo_ARN_InvHistoryHeader.InvoiceDate) Between #8/12/2006# And #8/20/2006#));

SELECT DISTINCT dbo_ups_invoices.TrackingID, dbo_ups_invoices.InvoiceDate
FROM dbo_ups_invoices
WHERE (((dbo_ups_invoices.InvoiceDate)=#8/19/2006#));