Solved

complicated query

Posted on 2016-11-02
15
51 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 119

Expert Comment

by:Rey Obrero
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
 

Author Comment

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

Accepted Solution

by:
Rey Obrero 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

914 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now