Solved

complicated query

Posted on 2016-11-02
15
61 Views
Last Modified: 2016-11-02
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
0
Comment
Question by:urjudo
  • 7
  • 4
  • 4
15 Comments
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41870604
Try using Mid() instead of Right() ..

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


ET
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41870605
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
 

Author Comment

by:urjudo
ID: 41870615
IIf(Mid([Child]![Calendar],10)="5"  still doesn't work
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:urjudo
ID: 41870620
I pull these a from a table
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 41870622
did you try my post?

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

IIf(Mid([Child]![Calendar],10, 1)="5"
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41870643
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
 

Author Comment

by:urjudo
ID: 41870650
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
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41870658
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41870660
this should do it
 IIf(Mid([Child]![Calendar],10, 1)="4" And  IIf(Mid([Child]![Calendar],10, 1)="9"
0
 

Author Comment

by:urjudo
ID: 41870674
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41870684
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
 

Author Comment

by:urjudo
ID: 41870805
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
 

Author Closing Comment

by:urjudo
ID: 41870993
Sorry, did not see your last message for "not post any more query".  sorry for bother and thanks for helped.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41870998
Really ... I thought that was the solution I suggested and provided.

No points???

ET
0
 

Author Comment

by:urjudo
ID: 41871071
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

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question