IIF in Complex query

[agaskelluk]

I have the following quite long access query. It works fine but I want to be able to substitute [ItemDetails]![US_Cost] with either [ItemDetails]![US_Cost] OR [ItemDetails]![NEW_US_Cost] depending on whether [MyOtherTable]![SomeField] = 0 or 1 where this is the 1st record in the table MyOtherTable. I'm not sure how to include MyOtherTable in the query please help! Sample code please.

SELECT Level4.PEC, IIf([ItemDetails]![CPC]<>"",[ItemDetails]![CPC],[Level4]![CPC]) AS CPC, IIf([ItemDetails]![Description]<>"",[ItemDetails]![Description],[Level4]![Description]) AS Description, Level4.Quantity, IIf([Level4]![EngOnly]=False,IIf([Level4]![EngOnly50]=True,IIf([ItemDetails]![US_Cost]>0,[Level4]![Quantity]*[ItemDetails]![US_Cost]/2,[Level4]![Quantity]*[Level4]![Cost]/2),IIf([ItemDetails]![US_Cost]>0,[Level4]![Quantity]*[ItemDetails]![US_Cost],[Level4]![Quantity]*[Level4]![Cost])),0) AS USCost, IIf([ItemDetails]![US_List]>0,[Level4]![Quantity]*[ItemDetails]![US_List],[Level4]![Quantity]*[Level4]![Price]) AS USPrice, Level4.Family, ItemDetails.CD_RTS, ItemDetails.Sort, IIf([ItemDetails]![CPC]<>"",IIf([ItemDetails]![Discountable]=-1,"True","False"),"False") AS Discountable, Level4.EngOnly, Level4.EngOnlySymbol, Level4.EngOnly50, Level4.EngOnly50Symbol, ItemDetails.USListSource, IIf([ItemDetails]![CPC]<>"",[ItemDetails]![EuedCompliant],False) AS EuedCompliant, IIf([ItemDetails]![CPC]<>"",[ItemDetails]![EuedAffected],False) AS EuedAffected
FROM Level4 LEFT JOIN ItemDetails ON Level4.PEC = ItemDetails.ComponentCode;

 

[Galaxiom]

The USCost expression is already very unweildy and adding more will make it unbearable.
It should be converted to a function with SomeField supplied as one of the arguments.

I would also consider aliasing the source tables to single characters to shorten the expression.

 

[agaskelluk]

Thanks for the response.
I didn't want to supply an argument to the query for the reason that it would mean updating the client application to pass the argument. Is there not a way to read the record in somefield from a table within the query itself rather than passing it as a param?

Thanks.

 

[Galaxiom]

I don't mean an "argument" (usually called a "parameter") to the query.

You can simply include the other table in the query and use the value to control the IIF. If it has just one record it does not need to be joined to the existing table and won't affect the number of records returned.

However it make your IIF even more unweildy than it already is. That is why I have suggested a function.

Construct the logic in the function. A function takes arguments from the fields and returns a value. This will allow it to be spread out into a more manageable structure.

The other table can still be included in the query but its value passed to the function as an argument. Inside the function the result and logic is controlled by the values fed to it in the arguments.

In your query calling the function will look like this:
FieldName: MyFunction([field1], [field2], [othertable].[somefield])

Many new developers shy away from functions unnecessarily. They are not as hard as they look and are vastly superior to using expressions for many reasons.

 

[agaskelluk]

I think I understand what you are suggesting, but I don't know how to implement it.

I don't know how to get the 1st record from othertable and return othertable!somefield in my query without making a join which is not what I want. Could I ask you to help with the syntax to update my original query (listed below) to get this record?

SELECT Level4.PEC, IIf([ItemDetails]![CPC]<>"",[ItemDetails]![CPC],[Level4]![CPC]) AS CPC, IIf([ItemDetails]![Description]<>"",[ItemDetails]![Description],[Level4]![Description]) AS Description, Level4.Quantity, IIf([Level4]![EngOnly]=False,IIf([Level4]![EngOnly50]=True,IIf([ItemDetails]![US_Cost]>0,[Level4]![Quantity]*[ItemDetails]![US_Cost]/2,[Level4]![Quantity]*[Level4]![Cost]/2),IIf([ItemDetails]![US_Cost]>0,[Level4]![Quantity]*[ItemDetails]![US_Cost],[Level4]![Quantity]*[Level4]![Cost])),0) AS USCost, IIf([ItemDetails]![US_List]>0,[Level4]![Quantity]*[ItemDetails]![US_List],[Level4]![Quantity]*[Level4]![Price]) AS USPrice, Level4.Family, ItemDetails.CD_RTS, ItemDetails.Sort, IIf([ItemDetails]![CPC]<>"",IIf([ItemDetails]![Discountable]=-1,"True","False"),"False") AS Discountable, Level4.EngOnly, Level4.EngOnlySymbol, Level4.EngOnly50, Level4.EngOnly50Symbol, ItemDetails.USListSource, IIf([ItemDetails]![CPC]<>"",[ItemDetails]![EuedCompliant],False) AS EuedCompliant, IIf([ItemDetails]![CPC]<>"",[ItemDetails]![EuedAffected],False) AS EuedAffected
FROM Level4 LEFT JOIN ItemDetails ON Level4.PEC = ItemDetails.ComponentCode;


Thanks again.

 

[winshent]

Thanks for the response.
I didn't want to supply an argument to the query for the reason that it would mean updating the client application to pass the argument. Is there not a way to read the record in somefield from a table within the query itself rather than passing it as a param?

Thanks.

Wont you have to update the client application if your updating a query definition within the app?

 

[agaskelluk]

The client would get a new database. The client code (vb6 app.) would not change.