Query too Complex

[DaRTHY]

Hello My friends,

i checked from Forum and found many Thread about this Problem. May be i could not understand good but unfortunately i did not find my answer....

Normally this code is working. But it´s too complex because my code is getting bigger and bigger.

May be you can find any answer for this i can get little bit smaller or fix the problem.

I have a Main => 1. Sub Query => 3 more Sub Queries (for Bus,Truck,Engine)They are same only these are for 3 different Reports. (you can understand with picture little bit better)

My Main Query is like a Monster. There is 5 different Tables and almost same repeating SQL-code : (i ll send the codes part by part from design View.)

Erreich 3_0_0: IIf([Projektliste]![PEP Art] Like "*3.0";IIf([Ist ME] Is Not Null And [Ist ME] Not Like #01.01.1999#;"ME";IIf([Ist SOP] Is Not Null And [Ist SOP] Not Like #01.01.1999#;"SOP";IIf([Ist 0S] Is Not Null And [Ist 0S] Not Like #01.01.1999#;"0S";IIf([Ist PVS] Is Not Null And [Ist PVS] Not Like #01.01.1999#;"PVS";IIf([Ist VFF] Is Not Null And [Ist VFF] Not Like #01.01.1999#;"VFF";IIf([Ist LF] Is Not Null And [Ist LF] Not Like #01.01.1999#;"LF";IIf([Ist BF] Is Not Null And [Ist BF] Not Like #01.01.1999#;"BF";IIf([Ist DF] Is Not Null And [Ist DF] Not Like #01.01.1999#;"DF";IIf([Projektliste]![PEP Art] Like "Aggregate PEP 3.0";IIf([Ist BF] Is Not Null And [Ist BF] Not Like #01.01.1999#;"BFA";IIf([Ist DF] Is Not Null And [Ist DF] Not Like #01.01.1999#;"TF";Null)))))))))));Null)

Erreich 3_0_1: IIf([Projektliste]![PEP Art] Like "*3.0";IIf([Ist DE] Is Not Null And [Ist DE] Not Like #01.01.1999#;"DE";IIf([Ist KE] Is Not Null And [Ist KE] Not Like #01.01.1999#;"KE";IIf([Ist PF] Is Not Null And [Ist PF] Not Like #01.01.1999#;"PF";IIf([Ist PD] Is Not Null And [Ist PD] Not Like #01.01.1999#;"PD";IIf([Ist PP] Is Not Null And [Ist PP] Not Like #01.01.1999#;"PP";IIf([Ist PM] Is Not Null And [Ist PM] Not Like #01.01.1999#;"PM";Null))))));Null)

Erreich PEP_3: IIf([Erreich 3_0_0] Is Null;[Erreich 3_0_1];[Erreich 3_0_0])

Erreich 2_0_0: IIf([Projektliste]![PEP Art] Like "*2.1";IIf([Ist ME] Is Not Null And [Ist ME] Not Like #01.01.1999#;"MF";IIf([Ist SOP] Is Not Null And [Ist SOP] Not Like #01.01.1999#;"SF";IIf([Ist 0S] Is Not Null And [Ist 0S] Not Like #01.01.1999#;"DS";IIf([Ist VFF] Is Not Null And [Ist VFF] Not Like #01.01.1999#;"VF";IIf([Ist LF] Is Not Null And [Ist LF] Not Like #01.01.1999#;"DV";IIf([Ist BF] Is Not Null And [Ist BF] Not Like #01.01.1999#;"FM";IIf([Ist DF] Is Not Null And [Ist DF] Not Like #01.01.1999#;"VM2";Null)))))));Null)

Erreich 2_0_1: IIf([Projektliste]![PEP Art] Like "*2.1";IIf([Ist DE] Is Not Null And [Ist DE] Not Like #01.01.1999#;"VM1";IIf([Ist KE] Is Not Null And [Ist KE] Not Like #01.01.1999#;"KF";IIf([Ist PD] Is Not Null And [Ist PD] Not Like #01.01.1999#;"PF";IIf([Ist PM] Is Not Null And [Ist PM] Not Like #01.01.1999#;"PS";Null))));Null)

Erreich PEP_2: IIf([Erreich 2_0_0] Is Null;[Erreich 2_0_1];[Erreich 2_0_0])

Erreich MS: IIf([Erreich PEP_3] Is Null;[Erreich PEP_2];[Erreich PEP_3])

same code is repeating 1 more time for next MS (Milestone) And 1 more in Sup Query.

Now i need to write almost same code again, with this that ll be 4. times :

  Termin1_0: IIf([Nächster MS]="PS";[Plan PM];IIf([Nächster MS]="KA";[Plan PM];IIf([Nächster MS]="PM";[Plan PM];IIf([Nächster MS]="PPA";[Plan PP];IIf([Nächster MS]="PP";[Plan PP];IIf([PEP Art] Like "*2.1" And [Nächster MS]="PF";[Plan PD];IIf([Nächster MS]="PDA";[Plan PD];IIf([Nächster MS]="KF";[Plan KE];IIf([Nächster MS]="AE";[Plan KE];IIf([Nächster MS]="KE";[Plan KE];IIf([Nächster MS]="PFA";[Plan PF];IIf([PEP Art] Like "*3.0" And [Nächster MS]="PF";[Plan PF];IIf([Nächster MS]="VM1";[Plan DE];IIf([Nächster MS]="TE";[Plan DE]))))))))))))))
   
   
  Termin1_1: IIf([Nächster MS]="DE";[Plan DE];IIf([Nächster MS]="VM2";[Plan DF];IIf([Nächster MS]="TF";[Plan DF];IIf([Nächster MS]="DF";[Plan DF];IIf([Nächster MS]="FM";[Plan BF];IIf([Nächster MS]="BFA";[Plan BF];IIf([Nächster MS]="BF";[Plan BF];IIf([Nächster MS]="DV";[Plan LF];IIf([Nächster MS]="HSF";[Plan LF];IIf([Nächster MS]="LF";[Plan LF];IIf([Nächster MS]="DV";[Plan VFF];IIf([Nächster MS]="VFA";[Plan VFF];IIf([Nächster MS]="VFF";[Plan VFF];IIf([Nächster MS]="PVS";[Plan PVS]))))))))))))))
   
   
  Termin1_2: IIf([Nächster MS]="DS";[Plan 0S];IIf([Nächster MS]="0S";[Plan 0S];IIf([Nächster MS]="SF";[Plan SOP];IIf([Nächster MS]="SOP";[Plan SOP];IIf([Nächster MS]="MF";[Plan ME];IIf([Nächster MS]="ME";[Plan ME]))))))

The code is working BUT when i try to write :

TerminSOP: IIf([Termin1_0] Is Null;[Termin1_1];[Termin1_0])

Im getting error....

is that possible, can i write this code more basic or small ? If you want i can send the all query but i dont think that ll be necessary.

Thanks for your helps

 

[CJ_London]

looks like most of it is testing for nulls so consider using the nz function. Also consider your criteria and joins- are there opportunities to exclude records where there are nulls

for the nz function this

Erreich MS: IIf([Erreich PEP_3] Is Null;[Erreich PEP_2];[Erreich PEP_3])

would be replaced with

Erreich MS: nz([Erreich PEP_3];[Erreich PEP_2])

replace your 'like's and not like's with'=' or '<>' where you are comparing the whole field

Erreich 3_0_0: IIf([Projektliste]![PEP Art] Like "*3.0";
IIf([Ist ME] Is Not Null And [Ist ME] <>#01.01.1999#;"ME";IIf([Ist SOP] Is Not Null And [Ist SOP] <>#01.01.1999#;"SOP";IIf([Ist 0S] Is Not Null And [Ist 0S] <>#01.01.1999#;"0S";IIf([Ist PVS] Is Not Null And [Ist PVS] <>#01.01.1999#;"PVS";IIf([Ist VFF] Is Not Null And [Ist VFF] <>#01.01.1999#;"VFF";IIf([Ist LF] Is Not Null And [Ist LF]<>#01.01.1999#;"LF";

IIf([Ist BF] Is Not Null And [Ist BF] <>#01.01.1999#;"BF";IIf([Ist DF] Is Not Null And [Ist DF] <>#01.01.1999#;"DF";IIf([Projektliste]![PEP Art] = "Aggregate PEP 3.0";IIf([Ist BF] Is Not Null And [Ist BF] <>#01.01.1999#;"BFA";IIf([Ist DF] Is Not Null And [Ist DF] <>#01.01.1999#;"TF";Null)))))))))));Null)

look at some of your code - this bit from above
iif([Ist ME] Is Not Null And [Ist ME] <>#01.01.1999#;

could be perhaps be simplified to (note the greater than)

iif(nz([Ist ME])>#01.01.1999#;

 

[DaRTHY]

okey thanks i ll try it.