467,116 Members | 1,221 Online

# due to formula "IIf ( "argue", 'true', 'false')" makes my query slow

 One another problem I have used a formula in the calculated field of a query as - IIf( [A] <=[b], (IIf([A]<=[C], [A], [C]), (IIf ([b]<=[C], [C], [b])) the above formula is working properly to give the lowest of [A], [b] and [C] but the problem is that the query is some slow as compared to other query not having such formula. At present, there is only one record in the query to calculate and to show. I fear if what will happen if there will be lots of records. what to do can i use - min([A], [b], [C]) - like expression Sep 14 '06 #1
• viewed: 3187
Share:
4 Replies
 Expert 1GB Strange! In one record to have slow performance! Expressions like this I use in big tables like 500 000 records and I don't have a big slow down! Maybe IIF isn't your problem? :) Sep 14 '06 #2
 Strange! In one record to have slow performance! Expressions like this I use in big tables like 500 000 records and I don't have a big slow down! Maybe IIF isn't your problem? :) I expressed what i felt. Well your post give me confidence on this type of formula. I want to aske whether your table contians - IIf (IIf (IIf())) - type of formula which is complex. Sep 14 '06 #3
 Expert 1GB See this! This returns result no for more than a few seconds! And in the concerned tables / queries there is 30000 - 40000 records! Expand|Select|Wrap|Line Numbers SELECT CROSSTAB_NORMS.[0] AS Продукт, arround(IIf([4]="Qty_Piece",Sum([Cadeaux_Commandes01].[Qty])*[CROSSTAB_NORMS].[Qty],Sum([Cadeaux_Commandes01].[Qty])/param_info(show_el_id(16,[1]),0,"COL01N0012")*[CROSSTAB_NORMS].[Qty]),1) AS Result FROM Cadeaux_Commandes01 INNER JOIN CROSSTAB_NORMS ON Cadeaux_Commandes01.ART = CROSSTAB_NORMS.[1] WHERE (((CROSSTAB_NORMS.[2])="Conditionnement") AND ((CROSSTAB_NORMS.[3])="Atelier") AND ((IIf(([5] Is Null) Or ([5]="All_markets"),True,IIf(Mid([5],1,2)=Mid([n°Cde],1,2),True,False)))=True)) GROUP BY CROSSTAB_NORMS.[0], CROSSTAB_NORMS.[1], CROSSTAB_NORMS.[4], CROSSTAB_NORMS.Qty;   Sep 14 '06 #4
 See this! This returns result no for more than a few seconds! And in the concerned tables / queries there is 30000 - 40000 records! Expand|Select|Wrap|Line Numbers SELECT CROSSTAB_NORMS.[0] AS Продукт, arround(IIf([4]="Qty_Piece",Sum([Cadeaux_Commandes01].[Qty])*[CROSSTAB_NORMS].[Qty],Sum([Cadeaux_Commandes01].[Qty])/param_info(show_el_id(16,[1]),0,"COL01N0012")*[CROSSTAB_NORMS].[Qty]),1) AS Result FROM Cadeaux_Commandes01 INNER JOIN CROSSTAB_NORMS ON Cadeaux_Commandes01.ART = CROSSTAB_NORMS.[1] WHERE (((CROSSTAB_NORMS.[2])="Conditionnement") AND ((CROSSTAB_NORMS.[3])="Atelier") AND ((IIf(([5] Is Null) Or ([5]="All_markets"),True,IIf(Mid([5],1,2)=Mid([n°Cde],1,2),True,False)))=True)) GROUP BY CROSSTAB_NORMS.[0], CROSSTAB_NORMS.[1], CROSSTAB_NORMS.[4], CROSSTAB_NORMS.Qty;   Oh its amazing. Now I think that time to be taken by MS Access for such type of query is one or two seconds irrespective of the numbers of records. Thanks I will go ahead with this formula. thanks a lot. I will first feed numbers of recods in the table and watch the speed. Thax a lot Sep 14 '06 #5