UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Problem With Iif In Query Criteria, Access 2016    
 
   
ordnance1
post Apr 3 2020, 12:12 PM
Post#1



Posts: 768
Joined: 7-May 11



I have placed this query in the Row Source for my combobox. It works fine without the where statement, but returns nothing when I add thew Where Statement.

So what I am trying to do:
If the user selects Charter Revoked on opt_View The TempVar is set to 4. If the user selects Charter Returned the TempVar is set to 3 and so on. If I make the Where statement read as shown below it works fine. My problem is that if the user selects All Active (5) I need this to return any record where the Status is less than 5, and the Where statement I created does not work

CODE
WHERE (((IIf([AgentCompleted] Is Not Null,6,IIf([CharterRevoked] Is Not Null,4,IIf([CharterReturned] Is Not Null,3,IIf([CharterReturn_Requested] Is Not Null,2,IIf([CharterSuspended] Is Not Null,1))))))=[TempVars]![View]))


CODE
SELECT DISTINCT dbo_Clubs.Club_ID, IIf(Nz([dbo_Profit_Loss].[Agent_ID],0)>0,"P & L","         ") AS [P&L], dbo_ClubCategory.ClubCategory, dbo_Clubs.ClubNumber, dbo_Clubs.ClubName, [City_Physical] & ", " & [Abbreviation] AS Location, [City_Physical] & " - " & [ClubNumber] & " " & [ClubCategory] AS FolderName, IIf(Nz([dbo_Profit_Loss].[Agent_ID],0)>0,"P & L","         ") & " " & [ClubCategory] & " " & [ClubNumber] & " - " & [City_Physical] & ", " & [Abbreviation] & "    " & [ClubName] AS GroupName, dbo_Agents.Agent_ID, dbo_Clubs.City_Physical, dbo_Clubs.ClubCategory_ID, dbo_Clubs.AgentAssigned, dbo_Clubs.CharterSuspended, dbo_Clubs.CharterReturn_Requested, dbo_Clubs.CharterReturned, dbo_Clubs.CharterRevoked, dbo_Clubs.AgentCompleted, IIf([AgentCompleted] Is Not Null,6,IIf([CharterRevoked] Is Not Null,4,IIf([CharterReturned] Is Not Null,3,IIf([CharterReturn_Requested] Is Not Null,2,IIf([CharterSuspended] Is Not Null,1))))) AS Status, [City_Physical] & " - " & [ClubNumber] & " " & [ClubCategory] AS FileName
FROM dbo_Agents INNER JOIN ((dbo_State RIGHT JOIN ((dbo_Clubs INNER JOIN dbo_ClubCategory ON dbo_Clubs.ClubCategory_ID = dbo_ClubCategory.ClubCategory_ID) LEFT JOIN dbo_Profit_Loss ON dbo_Clubs.Club_ID = dbo_Profit_Loss.Club_ID) ON dbo_State.State_ID = dbo_Clubs.State_ID_Physical) INNER JOIN dbo_Intersection_AgentClub ON dbo_Clubs.Club_ID = dbo_Intersection_AgentClub.Club_ID) ON dbo_Agents.Agent_ID = dbo_Intersection_AgentClub.Agent_ID

WHERE (((IIf([AgentCompleted] Is Not Null,6,IIf([CharterRevoked] Is Not Null,4,IIf([CharterReturned] Is Not Null,3,IIf([CharterReturn_Requested] Is Not Null,2,IIf([CharterSuspended] Is Not Null,1))))))=IIf([TempVars]![View]<>5,[TempVars]![View],(IIf([AgentCompleted] Is Not Null,6,IIf([CharterRevoked] Is Not Null,4,IIf([CharterReturned] Is Not Null,3,IIf([CharterReturn_Requested] Is Not Null,2,IIf([CharterSuspended] Is Not Null,1))))))<6)))

ORDER BY dbo_Clubs.ClubNumber, dbo_ClubCategory.ClubCategory;
Go to the top of the page
 
theDBguy
post Apr 3 2020, 12:33 PM
Post#2


UA Moderator
Posts: 78,121
Joined: 19-June 07
From: SunnySandyEggo


Hi. What does the TempVar get set to if the user selects All Active?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ordnance1
post Apr 3 2020, 12:47 PM
Post#3



Posts: 768
Joined: 7-May 11



It gets set to 5.
Go to the top of the page
 
ordnance1
post Apr 3 2020, 02:18 PM
Post#4



Posts: 768
Joined: 7-May 11



Solved:

CODE
SELECT DISTINCT dbo_Clubs.Club_ID, IIf(Nz([dbo_Profit_Loss].[Agent_ID],0)>0,"P & L","         ") AS [P&L], dbo_ClubCategory.ClubCategory, dbo_Clubs.ClubNumber, dbo_Clubs.ClubName, [City_Physical] & ", " & [Abbreviation] AS Location, [City_Physical] & " - " & [ClubNumber] & " " & [ClubCategory] AS FolderName, IIf(Nz([dbo_Profit_Loss].[Agent_ID],0)>0,"P & L","         ") & " " & [ClubCategory] & " " & [ClubNumber] & " - " & [City_Physical] & ", " & [Abbreviation] & "    " & [ClubName] AS GroupName, dbo_Agents.Agent_ID, dbo_Clubs.City_Physical, dbo_Clubs.ClubCategory_ID, dbo_Clubs.AgentAssigned, dbo_Clubs.CharterSuspended, dbo_Clubs.CharterReturn_Requested, dbo_Clubs.CharterReturned, dbo_Clubs.CharterRevoked, dbo_Clubs.AgentCompleted, IIf([AgentCompleted] Is Not Null,6,IIf([CharterRevoked] Is Not Null,4,IIf([CharterReturned] Is Not Null,3,IIf([CharterReturn_Requested] Is Not Null,2,IIf([CharterSuspended] Is Not Null,1))))) AS Status, [City_Physical] & " - " & [ClubNumber] & " " & [ClubCategory] AS FileName

FROM dbo_Agents INNER JOIN ((dbo_State RIGHT JOIN ((dbo_Clubs INNER JOIN dbo_ClubCategory ON dbo_Clubs.ClubCategory_ID = dbo_ClubCategory.ClubCategory_ID) LEFT JOIN dbo_Profit_Loss ON dbo_Clubs.Club_ID = dbo_Profit_Loss.Club_ID) ON dbo_State.State_ID = dbo_Clubs.State_ID_Physical) INNER JOIN dbo_Intersection_AgentClub ON dbo_Clubs.Club_ID = dbo_Intersection_AgentClub.Club_ID) ON dbo_Agents.Agent_ID = dbo_Intersection_AgentClub.Agent_ID

WHERE (((IIf([AgentCompleted] Is Not Null,6,IIf([CharterRevoked] Is Not Null,4,IIf([CharterReturned] Is Not Null,3,IIf([CharterReturn_Requested] Is Not Null,2,IIf([CharterSuspended] Is Not Null,1))))))=IIf([forms]![frm_Navigation].[opt_View]=1,1))) OR (((IIf([AgentCompleted] Is Not Null,6,IIf([CharterRevoked] Is Not Null,4,IIf([CharterReturned] Is Not Null,3,IIf([CharterReturn_Requested] Is Not Null,2,IIf([CharterSuspended] Is Not Null,1))))))=IIf([forms]![frm_Navigation].[opt_View]=2,2))) OR (((IIf([AgentCompleted] Is Not Null,6,IIf([CharterRevoked] Is Not Null,4,IIf([CharterReturned] Is Not Null,3,IIf([CharterReturn_Requested] Is Not Null,2,IIf([CharterSuspended] Is Not Null,1))))))=IIf([forms]![frm_Navigation].[opt_View]=3,3))) OR (((IIf([AgentCompleted] Is Not Null,6,IIf([CharterRevoked] Is Not Null,4,IIf([CharterReturned] Is Not Null,3,IIf([CharterReturn_Requested] Is Not Null,2,IIf([CharterSuspended] Is Not Null,1))))))=IIf([forms]![frm_Navigation].[opt_View]=4,4))) OR (((IIf([AgentCompleted] Is Not Null,6,IIf([CharterRevoked] Is Not Null,4,IIf([CharterReturned] Is Not Null,3,IIf([CharterReturn_Requested] Is Not Null,2,IIf([CharterSuspended] Is Not Null,1))))))=IIf([forms]![frm_Navigation].[opt_View]=6,6))) OR (((IIf([AgentCompleted] Is Not Null,6,IIf([CharterRevoked] Is Not Null,4,IIf([CharterReturned] Is Not Null,3,IIf([CharterReturn_Requested] Is Not Null,2,IIf([CharterSuspended] Is Not Null,1))))))=IIf([forms]![frm_Navigation].[opt_View]=5,1))) OR (((IIf([AgentCompleted] Is Not Null,6,IIf([CharterRevoked] Is Not Null,4,IIf([CharterReturned] Is Not Null,3,IIf([CharterReturn_Requested] Is Not Null,2,IIf([CharterSuspended] Is Not Null,1))))))=IIf([forms]![frm_Navigation].[opt_View]=5,2))) OR (((IIf([AgentCompleted] Is Not Null,6,IIf([CharterRevoked] Is Not Null,4,IIf([CharterReturned] Is Not Null,3,IIf([CharterReturn_Requested] Is Not Null,2,IIf([CharterSuspended] Is Not Null,1))))))=IIf([forms]![frm_Navigation].[opt_View]=5,3))) OR (((IIf([AgentCompleted] Is Not Null,6,IIf([CharterRevoked] Is Not Null,4,IIf([CharterReturned] Is Not Null,3,IIf([CharterReturn_Requested] Is Not Null,2,IIf([CharterSuspended] Is Not Null,1))))))=IIf([forms]![frm_Navigation].[opt_View]=5,4)))

ORDER BY dbo_Clubs.ClubNumber, dbo_ClubCategory.ClubCategory;
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    30th May 2020 - 09:42 AM