From: GMC -LSND on 24 Feb 2010 12:36
I have a table named TTIME with columns in it named: Casenum, Tidate,
I have a table named CLIENTSCASENOTES with columns: Casenum, CNdate, CNnote,
the column TIdate, in the TTIME table stores it's dates as mm/dd/yyyy
the column CNdate, in the CLIENTSCASENOTES tables stores its dates as
mm/dd/yyyy with the time on the end.
What I am trying to do is link the TTIME table with the CLIENTSCASENOTES
table and then get the following out of the query: All the records from
TTIME that do not have a record in the CLIENTSCASENOTES table with date
matching the date on the TTIME record by casenum and by snum.
It is perplexing to me.
From: GMC -LSND on 24 Feb 2010 12:41
I should add this is in Access 2007 linked to a SQL 2008 database.
From: KARL DEWEY on 24 Feb 2010 17:30
The format does not matter so long as they both are datatype DateTime.
Use this query to remove the time --
SELECT DateValue(CLIENTSCASENOTES.CNdate) AS NewDate, CLIENTSCASENOTES.*
Then left join the query to TTIME --
SELECT TTIME.Casenum, TTIME.Tidate, TTIME.Reason, TTIME.Snum
FROM TTIME LEFT JOIN qryClientNotesNewDate ON TTIME.Tidate =
qryClientNotesNewDate.NewDate AND TTIME.casenum =
qryClientNotesNewDate.casenum AND TTIME.snum = qryClientNotesNewDate.snum
WHERE qryClientNotesNewDate.NewDate Is Null AND
qryClientNotesNewDate.casenum Is Null AND qryClientNotesNewDate.snum IS Null;
Build a little, test a little.
"GMC -LSND" wrote:
> I should add this is in Access 2007 linked to a SQL 2008 database.