Complicated Query

[boblarson]

Okay, I'm a bit confused how the two date fields are supposed to work. So, sorry to make you do this, but can you try explaining again how you want one date over the other, if something exists?

 

[talkiechick]

So there are two date fields: Maturity Date and Rate Change Date. There are six date ranges needed, along with the six ranges, I have specific criteria for loan status, Call Report type and on top of that i have a calculated field "Call Report Balance." Basically i need the report to generate the listed criteria and have it allocate each record only once to each specific range.

No duplicates as in range 1 cannot have the same row of data as range 5. I need Rate Change date to be the priority date field, and many of those loans do not have a rate change date so if they do not and the rate change field is null then i need it to allocate the data to the correct maturity date range.

Is there a way to do this?

 

[boblarson]

Yes, you can create your OWN date field in the query depending on if there is one to begin with.

Something like:

MyDateField:IIf(IsNull([Rate Change]),[Maturity Date], [Rate Change])

And that is the basis where you would use your criteria for your dates ranges.

 

[talkiechick]

Would i do that with the union query or should i create a new query with all the date ranges and criteria with the above IIF statement?

 

[talkiechick]

Nevermind... I got the query to do what i wanted... at least for now. The best fix would be for the query to pull the oldest date field which ever field it came from Maturity or rate change. But let's hope this works.

 

[talkiechick]

Yes, you can create your OWN date field in the query depending on if there is one to begin with.

Something like:

MyDateField:IIf(IsNull([Rate Change]),[Maturity Date], [Rate Change])

And that is the basis where you would use your criteria for your dates ranges.

Bob,

I am wondering if you may have the answer to this query. You just seem to understand this all much better than. My query is working grreat from the last issue. Now I come to find that I can't give Rate Change priority over Maturity I need the query to pull the data only into the correct date range for either Maturity Date or Rate Change giving priority to the oldest date, whichever column it may come from.

Do you think there is a possible solution?

 

[boblarson]

Hmmm, I'll have to ponder that one for a bit. The oldest date of either and use Maturity Date if Rate Change Date is null, correct?

 

[talkiechick]

No... it doesn't matter if Rate Change Date is null... I just need to pull oldest date whatever date column it should come from. The Rate Change Date may have data or may not.

 

[boblarson]

So, perhaps this:

MyDateField:IIf(Nz([Rate Change Date],99999)> Nz(Maturity Date],99999),[Maturity Date], [Rate Change Date])

 

[talkiechick]

This is the SQL for the current Query where Rate Change Date takes priority, and reverts to Maturity date if Rate Change is Null. Maybe this will help with a solution.

SELECT [Loan Information].[Loan ID], [Loan Information].[Call Report Type], ([Loan Information]![Master Loan Balance]+[Loan Information]![Part_ Offset_1]+[Loan Information]![Part_Offset_2])+[Loan Information]![Discount] AS [Call Report Balance], [Loan Information].[Loan Status], IIf(IsNull([Rate Change Date]),[Maturity Date],[Rate Change Date]) AS MyDateField, IIf([MyDateField]<#3/31/2010#+91,"Less than 3 M",IIf([MyDateField] Between #3/31/2010#+92 And #3/31/2010#+365,"3M - 1 Y",IIf([MyDateField] Between #3/31/2010#+366 And #3/31/2010#+1095,"1 Y - 3 Y",IIf([MyDateField] Between #3/31/2010#+1096 And #3/31/2010#+1825,"3Y - 5 Y",IIf([MyDateField] Between #3/31/2010#+1826 And #3/31/2010#+5475,"5 Y-15 Y",IIf([MyDateField]>#3/31/2010#+5475,"greater than 15 Y")))))) AS DateRange
FROM [Loan Information]
WHERE ((([Loan Information].[Call Report Type]) In (12,13,14)) AND (([Loan Information].[Loan Status])="ACTIVE")) OR ((([Loan Information].[Call Report Type]) In (12,13,14)) AND (([Loan Information].[Loan Status])="ACTIVE"));

 

[talkiechick]

Not working, for some reason it gives me the same results as my query.

 

[boblarson]

Maybe I got the < switched around and it should be >

 

[talkiechick]

Actually it won't let me put it into the column. the expression keeps disappearing. How should i enter it SQL?

It says maturity date doesn't have an operator

 

[boblarson]

You are putting the expression in the area marked FIELD: ?

 

[talkiechick]

It was missing a bracket was why it wasn't working originally. Now it is asking for MyDateField Parameter.

 

[boblarson]

Can you post a screenshot of what it looks like (the whole line) in the Query By Example Grid?

 

[talkiechick]

Attached is the screen shot with the expression builder open and also the screen shot after i try to run the query.

 

[boblarson]

It doesn't surprise me because according to the builder you didn't copy the code EXACTLY as I had written it.

Want to try again? This time copy it EXACTLY as written and REPLACE everything that is currently in the builder window with this.


MyDateField:IIf(Nz([Rate Change Date],99999)< Nz([Maturity Date],99999),[Maturity Date], [Rate Change Date])

 

[talkiechick]

Now is is saying that I have invalid syntax. That I have an operand without an operator

 

[boblarson]

Sorry, I forgot to add the missing bracket in when I put it back in there. I just edited it so try one more time.