|
|
|
Calculations and Query Criteria |
|---|
| message from =?Utf-8?B?U0NITllERVM=?= on 21 May 2004 |
I use calculations in queries constantly for my reports and forms. When I use multiple calculations in a query and I place criteria for that calculation, I get a paramter pop up for my first calculation. How can I avoid this?
|
| =?Utf-8?B?U0NITllERVM=?= replied to John Vinson on 21 May 2004 |
I's a large SELECT statement, with the condition on the bottom, I put a space where the calculations begin. I keep receiving a pop up for the paramter LESS NET VALUE
SELECT [Loan Data].[Loan Number], [Loan Data].B1LNAME, [Loan Data].B1FNAME, [Loan Data].[Investor Code], [Loan Data].[Lien Position], Address.[Property Address1], Address.[Property State], Address.[Property City], [Additional Mortgages].[Second Mortgage], [Additional Mortgages].[Third Mortgage], [Additional Mortgages].[Second Mortgage Lien], [Additional Mortgages].[Third Mortgage Lien], [Additional Mortgages].[Third Mortgage Payoff], [Additional Mortgages].[Third Mortgage Payoff Date], [Additional Mortgages].[Second Mortgage Payoff], [Additional Mortgages].[Second Mortgage Payoff Date], Address.[Property Zip Code], [Loan Data].[Attorney Fees], [Loan Data].[Original Loan Amount], [Loan Data].[Next Due Date], [Loan Data].[Investor Code], [Loan Data].[Origination Date], [Additional Mortgages].[First Mortgage Payoff], [Additional Mortgages].[First Mortgage Payoff Date], [Loan Data].[Corp Adv Balance], [Loan Data].[Current Appraisal Value], [Loan Data].[Current Appraisal Date], [Loan Data].[Original Appraisal Value], [Loan Data].[Original Appraisal Date],
[Current Appraisal Value]*0.9 AS [Net Value], [Current Appraisal Value]*0.05 AS Taxes, Date() AS [Today's Date], ((Date()-([Next Due Date]+45))/30)*(10+80) AS Inspections, 95 AS [App/BPO], IIf([Current Appraisal Value]*0.015<1500,1500,IIf([Current Appraisal Value]*0.015>1500,([Current Appraisal Value]*0.015),1500)) AS [Maintenance 15% of Prop Val], IIf([Net Value]*0.06<2500,2500,IIf([Net Value]*0.06>2500,([Net Value]*0.06))) AS [Real Estate Comm 10%], IIf([property State]="NY",[Current Appraisal Value]*0.0175,0) AS [NY Transfer], [Taxes]-[Corp Adv Balance]+[Inspections]+[App/BPO]+[Maintenance 15% of Prop Val]+[Real Estate Comm 10%]+[Attorney Fees] AS [Estimated Operating Costs], [Second Mortgage Payoff]+[Third Mortgage Payoff] AS [Superior Liens], [Superior Liens]+[Estimated Operating Costs] AS [Subtotal to Acquire], [Net Value] AS [Less Net Value], [Less Net Value]-[Subtotal to Acquire] AS [Total Net Proceeds], [Loan Data].[Total Delinquent Amount], [Loan Data].Portfolio
FROM ([Loan Data] INNER JOIN Address ON [Loan Data].[Loan Number] = Address.[Loan Number]) INNER JOIN [Additional Mortgages] ON [Loan Data].[Loan Number] = [Additional Mortgages].[Loan Number]
WHERE ((([Less Net Value]-[Subtotal to Acquire])<=10000));
|
| =?Utf-8?B?QWxsZW4gUGlua2xleQ==?= replied to =?Utf-8?B?U0NITllERVM=?= on 21 May 2004 |
What is [Net Value]? I don't see it defined anywher else. Also, if [Net Value] exists somewhere, why not just use it and not name it as an expression [Less Net Value]?
I may be wrong, but I don't know what fields are in the tables or queries.
|
| =?Utf-8?B?U0NITllERVM=?= replied to =?Utf-8?B?QWxsZW4gUGlua2xleQ==?= on 21 May 2004 |
Thanks for picking up on that redundancy, I picked up on it too and corrected it. . . [Less Net Value] is redundantly naming [net value] in the select statement, I removed it, adn kept [net value] as [net value]...[net value] is the first calculation in the query, I defined a field in the query and named it net value with the calculation in it. Later on in the query I use this calculation in another calculation, and I get the pop up.
|
| =?Utf-8?B?U0NITllERVM=?= replied to =?Utf-8?B?U0NITllERVM=?= on 21 May 2004 |
Okay, my query works now because I combined all calculations into one at the end to retrieve my results based off of my criteria. Can Access do this without making one gigantic calculation at the end where I base my criteria?
|
| John Vinson replied to =?Utf-8?B?U0NITllERVM=?= on 21 May 2004 |
Only occasionally. In my experience, you usually CANNOT reuse a
calculated expression in a later expression. You can sometimes get
away with it but I don't know what the criteria are for whether you
can or can't; I'll typically do all calculated expressions directly
from table fields, rather than building up intermediate calculated
fields.
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
|
| =?Utf-8?B?U0NITllERVM=?= replied to John Vinson on 24 May 2004 |
Okay. The reason why I have the multiple calculations is because users need to see those values on a form, all broken down. But thanks for the help all who posted.
|
| John Vinson replied to =?Utf-8?B?U0NITllERVM=?= on 24 May 2004 |
One possibility would be to do the first-level calculations in the
Query, and then use those expressions for further calculations in the
Control Source of textboxes on the form.
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
|
|
