Query problems

[mc13]

I need to be able to take an item from table1 and an item <br>from table2 and get the description from table3. The problem is the description in table3 could be one of two choices depending on the other tables, and in the table itself it is listed as other so you have to give it a new value.I would like to do this in a query rather than in code.

 

[DanAuber]

It sounds like you need to use the IIf function in the query builder - which operates as follows:<br><br>iif(test,do this if test true,do this if test false)<br><br>Thus the Description field in the result would be like this<br><br>iif(test to see which description I get,Description if test true, Description if test false)<br><br>Check out IIf in help<br><br>hope this helps <p>Dan Auber<br><a href=mailtoanAuber@aol.com>DanAuber@aol.com</a><br><a href=

http://members.aol.com/_ht_a/danauber/myhomepage/>Dan

Auber's Home Page</a><br>

 

[mc13]

this seems to limit the return in the recordset. I have a recordset that should return 15000 records but is only returning 900 I need all of the records but change only the description field in the ones that meet the parameters plus there are three different if's that could acure in the query<br>and they all have the same initial value of Other<br>Here is the working Query without the filtering iif's<br><br>SELECT tblApril_2000_Exp.[GL Unit], tblApril_2000_Exp.Acct, tblApril_2000_Exp.DeptID, tblApril_2000_Exp.Proj, tblApril_2000_Exp.[Resource Amount], tblProjects.Code, tblGL.Description, tblCostCenter.VPGroup, tblApril_2000_Exp.Period, tblApril_2000_Exp.Journal<br>FROM tblCostCenter INNER JOIN (tblProjects INNER JOIN (tblGL INNER JOIN tblApril_2000_Exp ON tblGL.GL = tblApril_2000_Exp.Acct) ON tblProjects.ProjectID = tblApril_2000_Exp.Proj) ON tblCostCenter.CostCenter = tblApril_2000_Exp.DeptID<br>WHERE (((tblApril_2000_Exp.Acct)&lt;&gt;&quot;601512&quot)<br>GROUP BY tblApril_2000_Exp.[GL Unit], tblApril_2000_Exp.Acct, tblApril_2000_Exp.DeptID, tblApril_2000_Exp.Proj, tblApril_2000_Exp.[Resource Amount], tblProjects.Code, tblGL.Description, tblCostCenter.VPGroup, tblApril_2000_Exp.Period, tblApril_2000_Exp.Journal;<br>

 

[DanAuber]

Sounds like you need to nest the iifs<br><br>I.e. you have 3 iif conditions, do 1 2 or 3 if it meets none of these - then do 4<br><br>iif(test1,1,iif(test2,2,iif(test3,3,4)))<br><br> <p>Dan Auber<br><a href=mailtoanAuber@aol.com>DanAuber@aol.com</a><br><a href=

http://members.aol.com/_ht_a/danauber/myhomepage/>Dan

Auber's Home Page</a><br>

 

[mc13]

Here is the iif condtions that returns an empty recordset<br><br><br>IIf([tblGL].[Description]=&quot;Other&quot;,IIf([tblApril_2000_Exp].[Acct]=&quot;607004&quot;,IIf([tblApril_2000_Exp].[DeptID]=&quot;13022&quot;,[tblGL].[Description]=&quot;Pc's&quot;,[tblGL].[Description]=&quot;HardWare&quot,IIf([tblApril_2000_Exp].[Acct]=&quot;607008&quot;,IIf([tblApril_2000_Exp].[DeptID]=&quot;13109&quot;,[tblGL].[Description]=&quot;Software&quot;,[tblGL].[Description]=&quot;Hardware&quot)),[tblGL].[Description]=[tblGL].[Description])