another query question

Hi Experts,
I have another query question.  I have a query below:
Cal/Dist: IIf([RMLOCATION] In ("Z (Dist 6)","H (Dist 6)","District 6x","District 6"),"District 6",IIf([RMLOCATION] In ("V (Dist 2)","District 2"),"District 2",IIf([RMLOCATION] In ("W (Dist 3)","G (Dist 3)","District 3"),"District 3",IIf([RMLOCATION] In ("X (Dist 4)","District 4"),"District 4",IIf([RMLOCATION] In ("Y (Dist 5)","District 5","Calendar87"),"District 5",IIf(Left([RMLOCATION],1)="C",[RMLOCATION]))))))

my question is how do I exclude the "calendar 87" on the IIf(Left([RMLOCATION],1)="C",[RMLOCATION]) since "Calendar 87 is included in the District 5 and I have to removed from the part of IIf(Left([RMLOCATION],1)="C",[RMLOCATION]).

Thanks,
urjudoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PatHartmanConnect With a Mentor Commented:
how do I exclude the "calendar 87" on the IIf(Left([RMLOCATION],1)="C",[RMLOCATION])

The answer is - You Don't.  I believe in another post, I explained to you that the IIf() exits as soon as something falls into the final true path.

Your code isn't working because you have a typo.  There is no space between Calendar and 87 in the IIf()
0
 
Rey Obrero (Capricorn1)Commented:
it will be best if you create a table with all the combination that you want.
the query will then be very simple using a join to the combination table.
0
 
urjudoAuthor Commented:
all calendars are in one table
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Rey Obrero (Capricorn1)Commented:
<all calendars are in one table > what table? is there a relationship to this table and the other table in the query?

how about the other parts of your IIF() statements?
0
 
urjudoAuthor Commented:
SELECT IIf([RMLOCATION] In ("Z (Dist 6)","H (Dist 6)","District 6x","District 6"),"District 6",IIf([RMLOCATION] In ("V (Dist 2)","District 2"),"District 2",IIf([RMLOCATION] In ("W (Dist 3)","G (Dist 3)","District 3"),"District 3",IIf([RMLOCATION] In ("X (Dist 4)","District 4"),"District 4",IIf([RMLOCATION] In ("Y (Dist 5)","District 5","Calendar87"),"District 5",IIf(Left([RMLOCATION],1)="C",[RMLOCATION])))))) AS [Cal/Dist]
FROM tblCourtRoom
GROUP BY IIf([RMLOCATION] In ("Z (Dist 6)","H (Dist 6)","District 6x","District 6"),"District 6",IIf([RMLOCATION] In ("V (Dist 2)","District 2"),"District 2",IIf([RMLOCATION] In ("W (Dist 3)","G (Dist 3)","District 3"),"District 3",IIf([RMLOCATION] In ("X (Dist 4)","District 4"),"District 4",IIf([RMLOCATION] In ("Y (Dist 5)","District 5","Calendar87"),"District 5",IIf(Left([RMLOCATION],1)="C",[RMLOCATION])))))), tblCourtRoom.STATUS
HAVING (((tblCourtRoom.STATUS)="Active"));
0
 
urjudoAuthor Commented:
Thanks!!! I did not see there is no space.  sorry
0
 
PatHartmanCommented:
PS - I agree with Rey.  This type of a conversion is better done with a cross-reference table.  I'm sure you are going to end up with more than one query that includes this logic and the table solves the entire problem in a consistent way.  You just change the table to move/add groupings.  The default is handled in the query.

Select ..., IIf(IsNull(tblGroup.GroupName), tblCourtRoom.RMLOCATION, GroupName) As Group
From tblCourtRoom Left Join tblGroup On tblCourtroom.RMLOCATION = tblGroup.RMLOCATION
0
All Courses

From novice to tech pro — start learning today.