I need query to lookup records for this year only. (1 Viewer)

raydan

Registered User.
Local time
Today, 02:43
Joined
Aug 24, 2005
Messages
28
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

AWF VIP
Local time
Today, 02:43
Joined
Jul 2, 2005
Messages
13,825
Try:
Code:
WHERE (((Loans.StartDate)>DateAdd("d",-32,Date() And ((Loans.EndDate)>DateAdd("[b]yyyy[/b]",-1,Date())))));
 

raydan

Registered User.
Local time
Today, 02:43
Joined
Aug 24, 2005
Messages
28
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

Registered User.
Local time
Today, 02:43
Joined
Aug 24, 2005
Messages
28
Isn't there a year now() or something like it that would work?
 

RuralGuy

AWF VIP
Local time
Today, 02:43
Joined
Jul 2, 2005
Messages
13,825
Try this:
Code:
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.
 
Last edited:

raydan

Registered User.
Local time
Today, 02:43
Joined
Aug 24, 2005
Messages
28
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

AWF VIP
Local time
Today, 02:43
Joined
Jul 2, 2005
Messages
13,825
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:43
Joined
Feb 19, 2002
Messages
43,506
SELECT Loans.CustomerID, Loans.LoanID, Loans.LoanAmount, Loans.StartDate, Loans.EndDate, Loans.LoanLender
FROM Loans
WHERE (Loans.StartDate Between Date() and DateAdd("d",-32,Date())) Or (Loans.EndDate Between DateAdd("d",-32,Date()) And Date());

Based on your text description, it doesn't matter when a loan ends as long as it starts within the designated time period and it doesn't matter when it started as long as it ends within the designated time period.

PS, raydan, don't use Now() when you really mean Date(). Now() includes the time of day and will cause problems when used in date compares.
 

Users who are viewing this thread

Top Bottom