complicated query

Hi Experts,
Sorry to bother again.  I have a complicate query which I want to shorter it but I tired few times and could not got it and need help.  here is my query:
Cal: IIf(Left([Child]![Calendar] ,1)="C" And IIf([CPTEAM]="IS","IS"),"IS",IIf([Child]![Calendar] In ("Calendar 31","Calendar 32","Calendar 33","Calendar 34","Calendar 35","Calendar 89","Calendar 99","Calendar C") And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/C",IIf([Child]![Calendar] In ("Calendar 51","Calendar 52","Calendar 53","Calendar 54","Calendar 55","Calendar 95","Calendar 97","Calendar E") And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/E",IIf([Child]![Calendar] In ("Calendar 41","Calendar 42","Calendar 43","Calendar 44","Calendar 45","Calendar 94","Calendar 98","Calendar D",) And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/D", IIf([Child]![Calendar] In ("Calendar 11") And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/11", IIf([Child]![Calendar] In ("Calendar 21","Calendar 22","Calendar 23","Calendar 24") And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/20s", IIf([Child]![Calendar] In ("Calendar 61","Calendar 62","Calendar 63","Calendar 64") And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/60s",IIf([Child]![Calendar] In ("Z (Dist 6)","H (Dist 6)","District 6x","Calendar 87","District 6","V (Dist 2)","District 2","W (Dist 3)","G (Dist 3)","District 3","X (Dist 4)","District 4","Y (Dist 5)","District 5") And IIf([CPTEAM]="SU","SU"),"SU"))))))))

the query says too long, so I have to shorter it

what I want to do is if Calendar 51,calendar 52, calendar 53, calendar 54, calendar 55, I want to use Cal: IIf(Right([Child]![Calendar],2)="5" And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/E") but I tried IIf(Right([Child]![Calendar],2)="5", it did not give me all calendar 51 to 55 instead it return empty, I also tried IIf(Right([Child]![Calendar],2)="5*", same empty result.

Thanks
urjudoAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
did you try my post?

if the value on the field is consistent with "Calendar ##"

IIf(Mid([Child]![Calendar],10, 1)="5"
0
 
Eric ShermanAccountant/DeveloperCommented:
Try using Mid() instead of Right() ..

IIf(Mid([Child]![Calendar],10)="5"


ET
0
 
Rey Obrero (Capricorn1)Commented:
like what I mentioned in the previous thread, you need to create a table.

anyway, try this

IIf(Mid([Child]![Calendar],Instr([Child]![Calendar]," ")+1,1)="5" And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/E")
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
urjudoAuthor Commented:
IIf(Mid([Child]![Calendar],10)="5"  still doesn't work
0
 
urjudoAuthor Commented:
I pull these a from a table
0
 
Eric ShermanAccountant/DeveloperCommented:
Yes ... I left off the 1 in my solution but the Mid function should work ... IIf(Mid([Child]![Calendar],10, 1)="5"

Load up a sample db ... I don't want to waste time trying to guess.

ET
0
 
urjudoAuthor Commented:
Yes, I did try the first one, it gave me error#, but the second one is working, how do I do if I have besides the 5's, like this one:

IIf([Child]![Calendar] In ("Calendar 41","Calendar 42","Calendar 43","Calendar 44","Calendar 45","Calendar 94","Calendar 98","Calendar D",) And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/D"

I can do IIf(Mid([Child]![Calendar],10, 1)="4" but how do I include the calendar 94, calendar 98 and calendar D into the group?
0
 
Eric ShermanAccountant/DeveloperCommented:
IIf(Mid([Child]![Calendar],10, 1)="4" OR  IIf(Mid([Child]![Calendar],10, 1)="5" OR  IIf(Mid([Child]![Calendar],10, 1)="9"  OR  IIf(Mid([Child]![Calendar],10, 1)="D"

ET
0
 
Rey Obrero (Capricorn1)Commented:
this should do it
 IIf(Mid([Child]![Calendar],10, 1)="4" And  IIf(Mid([Child]![Calendar],10, 1)="9"
0
 
urjudoAuthor Commented:
the calendar 89,94, 95,97,98 and 99, C,D,E are a bit complicate coz they in different team, 95 , 97  & E are in DC/E, 89,99 & C are in DC/C, 94, 98 & D are in DC/D,  that's why I'm having so much problem
0
 
Rey Obrero (Capricorn1)Commented:
I still insist that you create a table that will produce a combination of values and classify/group them.

not posting any more query
0
 
urjudoAuthor Commented:
okay, I made some changes on this long query, but when I tried to run, it still saying the query is too long, what else I can modify

Cal: IIf(Left([Child]![Calendar],1)="C" And IIf([CPTEAM]="IS","IS"),"IS",IIf(Mid([Child]![Calendar],10,1)="1" And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/11",IIf(Mid([Child]![Calendar],10,1)="2" And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/20s",IIf(Mid([Child]![Calendar],10,1)="6" And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/60s",IIf(Mid([Child]![Calendar],10,1)="3" And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/D",IIf([Child]![Calendar] In ("Calendar 89","Calendar 99","Calendar C") And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/C",IIf(Mid([Child]![Calendar],10,1)="4" And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/D",IIf([Child]![Calendar] In ("Calendar 94","Calendar 98","Calendar D") And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/D",IIf(Mid([Child]![Calendar],10,1)="5" And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/D",IIf([Child]![Calendar] In ("Calendar 95","Calendar 97","Calendar E") And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/E", IIf((Left([Child]![Calendar],1)="D" Or "Y" Or "G" Or "H" Or "V" Or "W" Or "X" Or "Z") And IIf([CPTEAM]="SU","SU"),"SU",IIf([Child]![Calendar] In ("Calendar 87") And IIf([CPTEAM]="SU","SU"),"SU"))))))))))))
0
 
urjudoAuthor Commented:
Sorry, did not see your last message for "not post any more query".  sorry for bother and thanks for helped.
0
 
Eric ShermanAccountant/DeveloperCommented:
Really ... I thought that was the solution I suggested and provided.

No points???

ET
0
 
urjudoAuthor Commented:
Sorry Eric Sherman, I click too quick and not realized to gave you the point because when I read the last message for "not posting any more query", I feel so bad to keep asking your guys questions but because I'm having problem for the query, if I can figure it out, I wouldn't be bother you all.  Let me know if there any way that I can give you some points?  please accept my apology.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.