?
Solved

complicated query

Posted on 2016-11-02
15
Medium Priority
?
76 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

840 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