I need query to lookup records for this year only.

[raydan]

SELECT Loans.CustomerID, Loans.LoanID, Loans.LoanAmount, Loans.StartDate, Loans.EndDate, Loans.LoanLender
FROM Loans
WHERE (((Loans.StartDate)>DateAdd("d",-32,Date() And ((Loans.EndDate)>DateAdd("y",-1,Date())))));

I want my query to do a monthy lookup of bussiness where a loan is either opened or closed in the last 32 days. This works except its pulling up records from all previous years. I tried to filter the year as shown above, of course its not working. So what do i have to do? Thanks for any help in advanced.


Scott

 

[RuralGuy]

Try:

WHERE (((Loans.StartDate)>DateAdd("d",-32,Date() And ((Loans.EndDate)>DateAdd("[b]yyyy[/b]",-1,Date())))));

 

[raydan]

That didnt change anything. Thanks though. I have on record that fits criteria except its in '04, so I dont want to see it.

 

[raydan]

Isn't there a year now() or something like it that would work?

 

[RuralGuy]

Try this:

WHERE (((Loans.StartDate)>DateAdd("d",-32,Date()[COLOR=Red][b])[/b][/COLOR] _
And ((Loans.EndDate)>DateAdd("yyyy",-1,Date()))));

I missed it the 1st time around.

 

[raydan]

WHERE (((Loans.StartDate)>DateAdd("d",-32,Date()) And ((Loans.EndDate)>DateAdd("yyyy",-1,Date()))));



I pasted this in and now I get 0 records in the query.

 

[RuralGuy]

Is it possible you have no records that meet the current criteria? Temporarily change the StartDate test to (-150) and see if that picks up any records. I tested the syntax on my db and it works just fine.