Updating Date Between A Date Range 1

[TJLearn]

Hi Everyone,

Can anyone please help me with some coding for my update problem. I am in a rush and not so good about it. Thanks!

I have 2 tables: tblLoans and tblStates as below:

tblLoans:
LoanNum FundingDate State PlannedDate
1 1/1/02 CA
2 10/5/02 CA
3 12/17/03 CA
4 10/10/03 TX


tblStates:
State Days StartDate EndDate
CA 5 1/1/1995 12/31/2000
CA 10 1/1/2002 12/31/2002
CA 11 1/1/2003 12/31/2003
TX 10 1/1/1995 12/31/2003

What I need to do is to fill out the PlannedDate field in the tblLoans table. The criteria are:
1. tblLoans.State = tblStates.State
2. tblLoans.FundingDate between tblStates.StartDate AND tblStates.EndDate
3. Add Days to the tblLoans.FundingDate from the record that has tblStates.StartDate and tblStates.EndDate the tblLoans.FundingDate falls in between. To summarize the 3 criteria, I need to match the state columns in both tblLoans and tblStates tables. Then take the FundingDate of each loan in the tblLoans table and see if it falls in betwwen a date range of StartDate and EndDate in the tblStates table. If it falls in between a date range, then take the FundingDate and add the # of days from the Days column in the tblStates table.

For example, the tblLoans table should look like this after getting updated:

LoanNum FundingDate State PlannedDate
1 1/1/02 CA 1/11/02
2 10/5/02 CA 10/15/02
3 12/17/03 CA 12/28/03
4 10/10/03 TX 10/20/03

Any help will be very appreciated!

 

[oharab]

You can create a query that will do this for you. Something like


SELECT
tblLoan.LoanNum,
tblLoan.FundingDate,
tblLoan.State,
tblLoan.FundingDate + tblStates.Days AS PlannedDate

FROM
tblLoan,
tblStates

WHERE
tblLoan.State=tblStates.State AND
tblLoan.FundingDate Between tblStates.StartDate and tblStates.EndDate


hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[TJLearn]

Hi Ben,

Your statement works! Thank you so much.