Find Match by Date Range

[soldat452002]

Hello,

I have a problem keeping up all night, I have to find the Status of each Producer ID found on the tblStatus per Date Range. So the results I am looking for is I'm able bring over the Status from my TblStatus.Status to tblProducer.Status by Date Range criteria. Can anyone help with this please?

ProducerId Year State DateEff DateExp Status
0000338191 2016 OR 1/1/2016 1/1/2200 TEXP


tblProducer

ProducerId Year State DateEff DateExp Status
0000338191 2016 OR 1/1/2016 1/1/2200
0000338191 2015 OR 1/1/2015 12/31/2015
0000338191 2014 OR 1/1/2014 12/31/2014
0000338191 2013 OR 1/1/2013 12/31/2013
0000338191 2012 OR 1/1/2012 12/31/2012
0000338191 2011 OR 1/1/2011 12/31/2011
0000338191 2010 OR 1/1/2010 12/31/2010
0000338191 2009 OR 1/1/2009 12/31/2009
0000338191 2008 OR 1/1/2008 12/31/2008
0000338191 2007 OR 1/1/2007 12/31/2007
0000338191 2006 OR 1/1/2006 12/31/2006

TblStatus

ProducerID State Status Eff Exp
0000338191 OR TERMREL 8/7/2015 4/30/2016
0000338191 OR COMPLETE 10/3/2013 8/6/2015
0000338191 OR TEXP 5/1/2016 01/01/2200

[ranman256]

UPDATE tblProducer INNER JOIN tblStatus ON (tblProducer.State = tblStatus.State) AND (tblProducer.ProducerId = tblStatus.ProducerId) SET tblProducer.Status = [tblStatus].[Status]
WHERE (((tblProducer.DateEff) Between [tblStatus].[DateEff] And [tblStatus].[DateExp])) OR (((tblProducer.DateExp) Between [tblStatus].[DateEff] And [tblStatus].[DateExp]));

[CJ_London]

cross posted here

http://www.access-programmers.co.uk/...92#post1497592

OP already advised of the dangers of cross posting