Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

complicated query

Posted on 2016-11-02
15
Medium Priority
?
73 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

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!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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.
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.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

618 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