Parameter in Query not working correctly

[Bongard]

Hi, I am using a parameter in two different fields of a query. The
first is (As Of Date) where the Criteria is <=[Enter an As of Date] and
that is working fine. The second parameter is the one that is not
working. The field is a datediff field that looks like this:

DateDiff("d",[DATE],[Enter an as of Date])
criteria is: >=[Aged Days]

Where [DATE] is the loan origination date and the [Enter an as of Date]
is going to cut off transactions that occurred after the As Of date.
The idea with the second parameter is to only display loans that have
aged greater than x amount of days by the as of date and to show all
transactions on those loans that occurred before the as of date.

For example loan #123
[Date] [TranDate] [Amount]
7/1/06 7/5/06 50.00
7/1/06 8/5/06 100.00
7/1/06 9/5/06 150.00

If the user selcted an as of date (8/10/06) and AgedDays (60) this loan
should not display. Instead the first parameter is working and only
shows the first two transactions in the query but the second parameter
does not eliminate the results saying the loan hasn't aged 60days. I
would expect to see no results for this loan number when I ran the
query with these parameters.

Thanks so much!
Brian

 

[Jeff L]

Is the criteria of the query on the same line in design view? If not
it needs to be. The reason I ask is because it sounds like the query
has been designed to return all records that meet Criteria1 OR
Criteria2. By putting the criteria on the same line, it changes the
query to select all records that meet Criteria1 AND Criteria2.

Side note:
It is not a good idea to name a field Date. Date is a function in
Access and a reserved word and thus should never be used as a variable
or field name in a table, form, or report. I would strongly suggest
that you change that name.

Hope that helps!

 

[Bongard]

Thanks for the info about that date field, I guess I didn't think of
that. The parameters are of course in a different column but they are
both on the same line, or row--the top row with the label criteria. Is
there something else I can test. When I enter a parameter date after
today's date (So every loan meets the first parameter) then the second
parameter seems to work a little better but still not perfect. But when
I select an as of date before today's date, the second parameter seems
to not work really at all to exclude any records.

Any other ideas?

Thanks in advance!

 

[John Spencer]

I don't understand. Your data diff function is going to return 40 days for
all three of the Date value (they are all 7/1/06).

 

[Bongard]

Correct--I am trying to get all loans to show that have aged more than
60 days, and all transactions on those loans that happened before the
as of date. So in this case because each transaction record for the
loan will return an age of 40 days then this loan because 40<60 should
not show up...But it does.

-Brian

 

[John Spencer]

I misunderstood what you wanted to do.

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

DO you want all loans that have aged at least 60 days (that is the
difference between the loan date and the last transaction date)? And for
those that have more than 60 days, the transactions that occurred before a
specified cutoff date?

If my understanding is now correct, perhaps you want something like the
following query.
SELECT *
FROM YourTable
WHERE [Loan#] in
(SELECT [Loan#]
FROM YourTable as Temp
GROUP BY [Loan#]
HAVING DateDiff("d",Temp.Date,Max(Temp.TranDate)) >=[Aged Days])
AND TranDate <=[Enter an As of Date]

 

[Bongard]

Alright, admittedly I know next to nothing about the SQL but here it
is:

SELECT [tbl_Loan Master].[Loan Number], [tbl_Loan Master].[Loan Names],
Avg(tbl_Transactions.Amount) AS Amount, tbl_Transactions.[System Date],
Max(DateDiff("d",[DATE],[Enter an as of Date])) AS Difference,
[tbl_Loan Master].City, [tbl_Loan Master].State, [tbl_Loan
Master].Analyst, [tbl_Loan Master].DATE, [tbl_Loan
Master].Correspondent, tbl_Transactions.[Effective Date],
tbl_Transactions.Direction, tbl_Transactions.[Inbound type],
tbl_Transactions.[Outbound Type], tbl_Transactions.[Denial Fee Type],
tbl_Transactions.[Transaction Notes], tbl_Transactions.Notes
FROM tbl_UpdateNotes RIGHT JOIN (((((([tbl_Loan Master] INNER JOIN
tbl_Transactions ON [tbl_Loan Master].[Loan Number] =
tbl_Transactions.[Loan Number]) LEFT JOIN tbl_LTLT1 ON [tbl_Loan
Master].[Loan Number] = tbl_LTLT1.LN1) LEFT JOIN tbl_LTLT3 ON [tbl_Loan
Master].[Loan Number] = tbl_LTLT3.LN3) LEFT JOIN tbl_LTLT4 ON [tbl_Loan
Master].[Loan Number] = tbl_LTLT4.LN4) LEFT JOIN tbl_LTLT5 ON [tbl_Loan
Master].[Loan Number] = tbl_LTLT5.LN5) LEFT JOIN tbl_LTLT2 ON [tbl_Loan
Master].[Loan Number] = tbl_LTLT2.LN2) ON tbl_UpdateNotes.LN =
[tbl_Loan Master].[Loan Number]
GROUP BY [tbl_Loan Master].[Loan Number], [tbl_Loan Master].[Loan
Names], tbl_Transactions.[System Date], [tbl_Loan Master].City,
[tbl_Loan Master].State, [tbl_Loan Master].Analyst, [tbl_Loan
Master].DATE, [tbl_Loan Master].Correspondent,
tbl_Transactions.[Effective Date], tbl_Transactions.Direction,
tbl_Transactions.[Inbound type], tbl_Transactions.[Outbound Type],
tbl_Transactions.[Denial Fee Type], tbl_Transactions.[Transaction
Notes], tbl_Transactions.Notes
HAVING (((tbl_Transactions.[System Date])<=[Enter an as of Date]) AND
((Max(DateDiff("d",[DATE],[Enter an as of Date])))>=[Aged Days]));

That just seems like one big mess to me.

Thanks though John,
Brian

 

[John Spencer]

That will take some analysis and puzzling to figure out what you are doing.

More importantly, I still don't understand what you WANT to do.

Was my question in the post unclear? Or was I correct in my interpretation?

Please try to restate what you want the query to return.