I have a tbl name recruit which I pull personnle information from and then insert into a table called ACN in an oracle Db.
I always need to pull all recruits from tblRecruit where they need an action in 8 days. However if they are in the ACN table I don't want to show them becuase this means they have an active record, however if that record gets canceled i want them to show up agian.
here is my current code. the problem is that they do not show up agian if they have a record that is cancelled in the ACN table.
sql = "SELECT r.intRecruitID, r.strLastName + ' ' + r.strFirstName + ' ' + r.strMiddlename as Fullname, r.strSSN, r.dtProcessing, r.strMOS, m.strMeps, r.strAFQT, r.strZip " _
& "FROM tblRecruit as r LEFT OUTER Join tblMeps as m on m.intMepsId = r.intMepsId Left Outer Join OPENQUERY(SIDPERS, 'Select SSN_SM from ACN.ACN_CNTL_NBR_TBL') as S " _
& "on r.strSSN = s.SSN_SM WHERE Convert(varchar(10), r.dtProcessing, 121) >= Convert(varchar(10), DATEADD(d, 4, '2002-09-27'), 121) AND Convert(varchar(10), r.dtProcessing, 121) " _
& "<= Convert(varchar(10), DATEADD(d, 5, '2002-09-27'), 121) AND s.SSN_SM IS NULL"
So if DT_EXPIRe is greater than or equall to today they should not show but if its expired and within the 8 day window above they should show up.