Nested IIF's with conditional statement in Query 2

[cwadams]

I want to compare two values. The value that is on my form and the value(s) that are in the query. Ex: "If Value on form for [Cond] is "A" and true; & value for that field in the query is: E, G, A, F, or P. I want to calculate a value. There are 5 different values that can be on Form. E,G,A,F,P (So in my query I will have 5 different statements similiar to one below)

AdjACond: IIf ([Forms]![frmSubject]![Cond] Like ‘A’, IIf ([Cond] Like ‘E’, -.20 * [SalePrice], IIF ([Cond] Like ‘G’, -.10 * [SalePrice], IIF ([Cond] Like ‘A’, 0, IIF ([Cond] Like ‘F’, .10 * [SalePrice], IIF ([Cond] Like ‘P’, .20 * [SalePrice],0))))))

The problem is I keep getting prompted 5 times for 'A', 'E', 'G', 'P' If at each prompt I enter the letter that it prompts me; I get the right value returned. Why am I getting prompted and how do I fix this?

 

[dhookom]

Access queries would use double quotes for delimiters rather than single quotes.

Also, you shouldn't use "Like" when you should be using "=". Also, you can't use [Cond] as an abbreviation for [Forms]![frmSubject]![Cond]

Also, I don't know how your sql statement can have a space between "IIf" and "(". Apparently you aren't copying and pasting your SQL view.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cwadams]

1. Single quotes work. Double quotes DO NOT in the statement below that I wrote in a Query.My belief was that you were SUPPOSED to use double quotes.Double don't work below.
----------
AdjBSMSub: IIf([Forms]![frmSubject]![FirstFourSeg] Like '*bsm*',IIf([FirstFourSeg] Like '*bsm*',0,10000),0)
----------
2. I was using "LIKE" to mimic what I had written when I wrote a condition using a wildcard.(see above)
3. [Cond] is the name of the field in the query that comes from a table. Whereas the other is the name of the field from a form.(not being used as an abbrev.)Trying to Compare.

My SQL View is pretty expanse thought I would write a snippet of the query.

 

[cwadams]

MY SQL view Code:
SELECT TOP 3 Temp.RecordID AS RecordID, Temp.Cond AS Cond, Temp.FirstFourSeg AS FirstFourSeg, IIf([Forms]![frmSubject]![FirstFourSeg] Like '*bsm*',IIf([FirstFourSeg] Like '*bsm*',0,10000),0) AS AdjBSMSub, IIf([FirstFourSeg] Like '*bsm*',IIf([Forms]![frmSubject]![FirstFourSeg] Like '*bsm*',0,-10000),0) AS AdjBSMComp, IIf([Forms]![frmSubject]![Cond]=[“A”],IIf([Cond]=[“E”],-0.2*[SalePrice]
,IIf([Cond]=[“G”],-0.1*[SalePrice],IIf([Cond]=[“A”],0,IIf([Cond]=[“F”],
0.1*[SalePrice],IIf([Cond]=[“P”],0.2*[SalePrice],0)))))) AS AdjACond
FROM [Select Temp.Property, Temp.RecordID, Temp.Stat, Temp.InfoCmfStatus, Temp.SlDate, Temp.SalePrice, Temp.AltAcctNum, Temp.MapTaxLot, Temp.MaintArea, Temp.Nbhd, Temp.CoPropCls, Temp.InstNos, Temp.TotAcres, Temp.Acreage, Temp.HeatAc, Temp.OwnerName, Temp.MhSpace, Temp.Situs, Temp.SitusCSZ, Temp.Baths, Temp.Bedrooms, Temp.BldgCls, Temp.ActYear, Temp.EffYrBlt, Temp.Cond, Temp.Area, Temp.Plumbing, Temp.FirstLandType, Temp.SegType, Temp.Class, Temp.Make, Temp.Model, Temp.Width, Temp.Length, Temp.ImpType, Temp.ImpId, Temp.LandTypes, Temp.FirstFourSeg
FROM Temp WHERE Temp.RecordID = 1
Union
Select Temp.Property, Temp.RecordID, Temp.Stat, Temp.InfoCmfStatus, Temp.SlDate, Temp.SalePrice, Temp.AltAcctNum, Temp.MapTaxLot, Temp.MaintArea, Temp.Nbhd, Temp.CoPropCls, Temp.InstNos, Temp.TotAcres, Temp.Acreage, Temp.HeatAc, Temp.OwnerName, Temp.MhSpace, Temp.Situs, Temp.SitusCSZ, Temp.Baths, Temp.Bedrooms, Temp.BldgCls, Temp.ActYear, Temp.EffYrBlt, Temp.Cond, Temp.Area, Temp.Plumbing, Temp.FirstLandType, Temp.SegType, Temp.Class, Temp.Make, Temp.Model, Temp.Width, Temp.Length, Temp.ImpType, Temp.ImpId, Temp.LandTypes,Temp.FirstFourSeg
FROM Temp WHERE Temp.RecordID = 2 and Temp.Property not in (Select Temp.Property FROM Temp WHERE Temp.RecordID < 2)
Union
Select Temp.Property, Temp.RecordID, Temp.Stat, Temp.InfoCmfStatus, Temp.SlDate, Temp.SalePrice, Temp.AltAcctNum, Temp.MapTaxLot, Temp.MaintArea, Temp.Nbhd, Temp.CoPropCls, Temp.InstNos, Temp.TotAcres, Temp.Acreage, Temp.HeatAc, Temp.OwnerName, Temp.MhSpace, Temp.Situs, Temp.SitusCSZ, Temp.Baths, Temp.Bedrooms, Temp.BldgCls, Temp.ActYear, Temp.EffYrBlt, Temp.Cond, Temp.Area, Temp.Plumbing, Temp.FirstLandType, Temp.SegType, Temp.Class, Temp.Make, Temp.Model, Temp.Width, Temp.Length, Temp.ImpType, Temp.ImpId, Temp.LandTypes,Temp.FirstFourSeg
FROM Temp WHERE Temp.RecordID = 3 and Temp.Property not in (Select Temp.Property FROM Temp WHERE Temp.RecordID < 3)
Union
Select Temp.Property, Temp.RecordID, Temp.Stat, Temp.InfoCmfStatus, Temp.SlDate, Temp.SalePrice, Temp.AltAcctNum, Temp.MapTaxLot, Temp.MaintArea, Temp.Nbhd, Temp.CoPropCls, Temp.InstNos, Temp.TotAcres, Temp.Acreage, Temp.HeatAc, Temp.OwnerName, Temp.MhSpace, Temp.Situs, Temp.SitusCSZ, Temp.Baths, Temp.Bedrooms, Temp.BldgCls, Temp.ActYear, Temp.EffYrBlt, Temp.Cond, Temp.Area, Temp.Plumbing, Temp.FirstLandType, Temp.SegType, Temp.Class, Temp.Make, Temp.Model, Temp.Width, Temp.Length, Temp.ImpType, Temp.ImpId, Temp.LandTypes,Temp.FirstFourSeg
FROM Temp WHERE Temp.RecordID = 4 and Temp.Property not in (Select Temp.Property FROM Temp WHERE Temp.RecordID < 4)
Union
Select Temp.Property, Temp.RecordID, Temp.Stat, Temp.InfoCmfStatus, Temp.SlDate, Temp.SalePrice, Temp.AltAcctNum, Temp.MapTaxLot, Temp.MaintArea, Temp.Nbhd, Temp.CoPropCls, Temp.InstNos, Temp.TotAcres, Temp.Acreage, Temp.HeatAc, Temp.OwnerName, Temp.MhSpace, Temp.Situs, Temp.SitusCSZ, Temp.Baths, Temp.Bedrooms, Temp.BldgCls, Temp.ActYear, Temp.EffYrBlt, Temp.Cond, Temp.Area, Temp.Plumbing, Temp.FirstLandType, Temp.SegType, Temp.Class, Temp.Make, Temp.Model, Temp.Width, Temp.Length, Temp.ImpType, Temp.ImpId, Temp.LandTypes,Temp.FirstFourSeg
FROM Temp WHERE Temp.RecordID = 5 and Temp.Property not in (Select Temp.Property FROM Temp WHERE Temp.RecordID < 5)
Union
Select Temp.Property, Temp.RecordID, Temp.Stat, Temp.InfoCmfStatus, Temp.SlDate, Temp.SalePrice, Temp.AltAcctNum, Temp.MapTaxLot, Temp.MaintArea, Temp.Nbhd, Temp.CoPropCls, Temp.InstNos, Temp.TotAcres, Temp.Acreage, Temp.HeatAc, Temp.OwnerName, Temp.MhSpace, Temp.Situs, Temp.SitusCSZ, Temp.Baths, Temp.Bedrooms, Temp.BldgCls, Temp.ActYear, Temp.EffYrBlt, Temp.Cond, Temp.Area, Temp.Plumbing, Temp.FirstLandType, Temp.SegType, Temp.Class, Temp.Make, Temp.Model, Temp.Width, Temp.Length, Temp.ImpType, Temp.ImpId, Temp.LandTypes,Temp.FirstFourSeg
FROM Temp WHERE Temp.RecordID = 6 and Temp.Property not in (Select Temp.Property FROM Temp WHERE Temp.RecordID < 6)
Union
Select Temp.Property, Temp.RecordID, Temp.Stat, Temp.InfoCmfStatus, Temp.SlDate, Temp.SalePrice, Temp.AltAcctNum, Temp.MapTaxLot, Temp.MaintArea, Temp.Nbhd, Temp.CoPropCls, Temp.InstNos, Temp.TotAcres, Temp.Acreage, Temp.HeatAc, Temp.OwnerName, Temp.MhSpace, Temp.Situs, Temp.SitusCSZ, Temp.Baths, Temp.Bedrooms, Temp.BldgCls, Temp.ActYear, Temp.EffYrBlt, Temp.Cond, Temp.Area, Temp.Plumbing, Temp.FirstLandType, Temp.SegType, Temp.Class, Temp.Make, Temp.Model, Temp.Width, Temp.Length, Temp.ImpType, Temp.ImpId, Temp.LandTypes,Temp.FirstFourSeg
FROM Temp WHERE Temp.RecordID = 7 and Temp.Property not in (Select Temp.Property FROM Temp WHERE Temp.RecordID < 7)]. AS TOPTHREE
ORDER BY Temp.RecordID;
*****
For some reason Access puts [] brackets around my strings[A]
[E],[G],etc.

 

[lespaul]

I would take that huge union query and save it qryUnion or something and then this query can be written as:

SELECT blah blah blah FROM qryUnion

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[cwadams]

SELECT qryTopThree.RecordID, IIf([Forms]![frmSubject]![Cond]=[“A”],IIf([Cond]=[“E”],-0.2*[SalePrice],
IIf([Cond]=[“G”],-0.1*[SalePrice],IIf([Cond]=[“A”],0,IIf([Cond]=[“F”],0.1*
[SalePrice],IIf([Cond]=[“P”],0.2*[SalePrice],0)))))) AS AdjACond
FROM qryTopThree;
*************************

I was frustrated...should of been thinking simple.

 

[PHV]

Replace this:
=[“A”],
with this:
='A',

and so on.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I wouldn't embed business calculations in a query like this unless it was all drive with data values rather than hard-coded values. I would create a function to replace
[red]IIf([Forms]![frmSubject]![Cond]=[“A”],IIf([Cond]=[“E”],-0.2*
[SalePrice],IIf([Cond]=[“G”],-0.1*[SalePrice],IIf([Cond]=[“A”],0,IIf
([Cond]=[“F”],0.1*[SalePrice],IIf([Cond]=[“P”],0.2*[SalePrice],0)))))) AS AdjACond[/red]
You could save the function in a module of "modBusinessCalcs" so that when your percentages or whatever change, you won't have to dig through sql statements.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cwadams]

This is the code that I Type in query:
AdjACond: IIf ([Forms]![frmSubject]![Cond] = ‘A’, IIf ([Cond] = ‘E’, -.20 * [SalePrice], IIF ([Cond] = ‘G’, -.10 * [SalePrice], IIF ([Cond] = ‘A’, 0, IIF ([Cond] = ‘F’, .10 * [SalePrice], IIF ([Cond] = ‘P’, .20 * [SalePrice],0))))))

However when I run the query...Access changes it to this:
AdjACond: IIf([Forms]![frmSubject]![Cond]=[‘A’],IIf([Cond]=[‘E’],-0.2*[SalePrice],
IIf([Cond]=[‘G’],-0.1*[SalePrice],IIf([Cond]=[‘A’],0,IIf([Cond]=[‘F’],
0.1*[SalePrice],IIf([Cond]=[‘P’],0.2*[SalePrice],0))))))

ACCESS is putting brackets in. I am not sure why it is doing this and then prompting me for 'A' or 'F' etc.

 

[PHV]

Replace this:
= ‘A’
with this:
= 'A'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I'm not sure why your SQL has ‘’ characters when they should be '. Are you typing this in Word and then pasting into your SQL? The values in your SQL don't appear to be simple single quotes.

I still think you should place this calc where it belongs which IMHO isn't the query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cwadams]

dhookom-
Let's say, I have a "Subject" it is in "Avg" condition. I have 3 comparables to that subject, in some sort of Condition. "avg","fair","Poor","excellent". If my subject is in Avg cond and my comparable is in poor condition then calculate a percentage. If comparable is in Excellent then calculate a different value. I don't get why access thinks I am wanting to prompt the user, when all I want is to compare the condition of my subject to the condition of the other three comparables.

 

[cwadams]

I was not aware that you cannot copy from WORD to Access.
PHV & dhookom "You are both 'right'"
I learned a valuable lesson here. Don't use WORD.
You both get a star for fast response and valuable post to me. Thanks
After 3 days.....Saved my weekend, thanks

 

[lespaul]

how do you even do that strange tick mark?

 

[PHV]

You may use NotePad instead of Word.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

how do you even do that strange tick
The magic is in the word's autocorrect feature.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886