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
> Query Criteria Issue, Access 2016    
 
   
ordnance1
post Aug 19 2019, 10:43 AM
Post#1



Posts: 659
Joined: 7-May 11



I have a query that populates a dropdown box. The criteria is assigned to the field User_ID.

If the UserType_ID = 2, I want to only see those records that belong to the User_ID returned by TempVars!User_ID. If UserType_ID = 1 (Admin) I want to see all records.

CODE
Like IIf([UserType_ID]=2,[TempVars]![User_ID],"*")


CODE
SELECT dbo_Aeries_Auxiliaries.Aeries_Auxiliaries_ID, dbo_Aeries_Auxiliaries.ClubNum, dbo_Aeries_Auxiliaries.Aeries_Auxiliaries_Type_ID, dbo_Aeries_Auxiliaries_Type.Aeries_Auxiliaries_Type, IIf(Nz([ClubName],"z")="z",[Aeries_Auxiliaries_Type] & " " & [dbo_Clubs].[ClubNum] & " - " & [dbo_Clubs].[City] & ", " & [Abbreviation],[Aeries_Auxiliaries_Type] & " " & [dbo_Clubs].[ClubNum] & " - " & [ClubName] & " - " & [dbo_Clubs].[City] & ", " & [Abbreviation]) AS AerieAuxiliaryName, dbo_Aeries_Auxiliaries.Completed, dbo_Aeries_Auxiliaries.Aerie_Auxiliary_Status_ID, [dbo_Clubs].[City] & " - " & [dbo_Clubs].[ClubNum] AS ShortName, dbo_Clubs.City, dbo_States.State, dbo_Aeries_Auxiliaries.User_ID, dbo_User.UserType_ID
FROM dbo_User INNER JOIN (dbo_Aeries_Auxiliaries_Type INNER JOIN ((dbo_Clubs INNER JOIN dbo_States ON dbo_Clubs.State_ID = dbo_States.State_ID) INNER JOIN dbo_Aeries_Auxiliaries ON dbo_Clubs.ClubNum = dbo_Aeries_Auxiliaries.ClubNum) ON dbo_Aeries_Auxiliaries_Type.Aeries_Auxiliaries_Type_ID = dbo_Aeries_Auxiliaries.Aeries_Auxiliaries_Type_ID) ON dbo_User.User_ID = dbo_Aeries_Auxiliaries.User_ID
GROUP BY dbo_Aeries_Auxiliaries.Aeries_Auxiliaries_ID, dbo_Aeries_Auxiliaries.ClubNum, dbo_Aeries_Auxiliaries.Aeries_Auxiliaries_Type_ID, dbo_Aeries_Auxiliaries_Type.Aeries_Auxiliaries_Type, IIf(Nz([ClubName],"z")="z",[Aeries_Auxiliaries_Type] & " " & [dbo_Clubs].[ClubNum] & " - " & [dbo_Clubs].[City] & ", " & [Abbreviation],[Aeries_Auxiliaries_Type] & " " & [dbo_Clubs].[ClubNum] & " - " & [ClubName] & " - " & [dbo_Clubs].[City] & ", " & [Abbreviation]), dbo_Aeries_Auxiliaries.Completed, dbo_Aeries_Auxiliaries.Aerie_Auxiliary_Status_ID, [dbo_Clubs].[City] & " - " & [dbo_Clubs].[ClubNum], dbo_Clubs.City, dbo_States.State, dbo_Aeries_Auxiliaries.User_ID, dbo_User.UserType_ID
HAVING (((dbo_Aeries_Auxiliaries.User_ID)=IIf([UserType_ID]=2,[TempVars]![User_ID],[TempVars]![Null])))
ORDER BY IIf(Nz([ClubName],"z")="z",[Aeries_Auxiliaries_Type] & " " & [dbo_Clubs].[ClubNum] & " - " & [dbo_Clubs].[City] & ", " & [Abbreviation],[Aeries_Auxiliaries_Type] & " " & [dbo_Clubs].[ClubNum] & " - " & [ClubName] & " - " & [dbo_Clubs].[City] & ", " & [Abbreviation]);


Attached File  Untitled_picture.png ( 309.65K )Number of downloads: 6


Go to the top of the page
 
theDBguy
post Aug 19 2019, 10:51 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,290
Joined: 19-June 07
From: SunnySandyEggo


Hi. How about maybe something like:
CODE
([UserType_ID]=2 AND TempVars![User_ID]) OR [UserType_ID]=1
Hope it helps...

PS. After looking at the image you posted, I think you should use a WHERE clause instead of a HAVING clause. For example:
CODE
WHERE (UserType_ID=2 AND User_ID=TempVars!User_ID) OR UserType_ID=1

--------------------
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
 
strive4peace
post Aug 19 2019, 11:08 AM
Post#3


strive4peace
Posts: 20,455
Joined: 10-January 04



I agree with theDBguy -- you probably want criteria in WHERE since that happens before aggregation is done.

I notice your screenshot uses inner joins, which means that records from the Auxilaries table won't display unless Type_ID, ClubNum and User_ID are filled. Also, the matching ClubNum will need to have State_ID filled too.

To change the join type:
- right-click on the join line and choose Join Properties

You have 3 choices:
"1" means that only records that both tables have will show -- this is what you have now.
"2" and "3" modify the join line so there is an arrow pointing away from the most important table. You will probably want to choose the option that shows all records from Auxilaries -- and the join line between Clubs and States should show all records from Clubs.

--------------------
have an awesome day,
crystal
Go to the top of the page
 
ordnance1
post Aug 19 2019, 11:22 AM
Post#4



Posts: 659
Joined: 7-May 11



Thanks for taking the time to reply.

I am afraid that your suggestion still did not produce the desired result. I just returned all records regardless of the UserType_ID.

What I am looking for is:

A criteria for field User_ID that checks if UserType_ID is equal to 1 (Admin) then set the criteria for field User_ID to return all records. If UserType_ID = 2 (User) then set the criteria for field User_ID to the value of TempVars!User_Id.
Go to the top of the page
 
ordnance1
post Aug 19 2019, 11:24 AM
Post#5



Posts: 659
Joined: 7-May 11



Thank you

Those are all mandatory fields which do not allow nulls. And I have changed to WHERE
This post has been edited by ordnance1: Aug 19 2019, 11:25 AM
Go to the top of the page
 
theDBguy
post Aug 19 2019, 11:27 AM
Post#6


Access Wiki and Forums Moderator
Posts: 76,290
Joined: 19-June 07
From: SunnySandyEggo


Hi. Can you please show us exactly how you applied the suggestion to your query? Thanks.

--------------------
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 Aug 19 2019, 11:47 AM
Post#7



Posts: 659
Joined: 7-May 11



I have it working now. i dropped the UserType_ID column and changed the reference in the criteria to TempVars!UserType_ID. The criteria now reads:

CODE
Like IIf([TempVars]![UserType_ID]=2,[TempVars]![User_ID],"*")



Here is the SQL as it stands now:

CODE
SELECT dbo_Aeries_Auxiliaries.Aeries_Auxiliaries_ID, dbo_Aeries_Auxiliaries.ClubNum, dbo_Aeries_Auxiliaries.Aeries_Auxiliaries_Type_ID, dbo_Aeries_Auxiliaries_Type.Aeries_Auxiliaries_Type, IIf(Nz([ClubName],"z")="z",[Aeries_Auxiliaries_Type] & " " & [dbo_Clubs].[ClubNum] & " - " & [dbo_Clubs].[City] & ", " & [Abbreviation],[Aeries_Auxiliaries_Type] & " " & [dbo_Clubs].[ClubNum] & " - " & [ClubName] & " - " & [dbo_Clubs].[City] & ", " & [Abbreviation]) AS AerieAuxiliaryName, dbo_Aeries_Auxiliaries.Completed, [dbo_Clubs].[City] & " - " & [dbo_Clubs].[ClubNum] AS ShortName, dbo_Clubs.City, dbo_States.State, dbo_Aeries_Auxiliaries.User_ID
FROM dbo_User INNER JOIN (dbo_Aeries_Auxiliaries_Type INNER JOIN ((dbo_Clubs INNER JOIN dbo_States ON dbo_Clubs.State_ID = dbo_States.State_ID) INNER JOIN dbo_Aeries_Auxiliaries ON dbo_Clubs.ClubNum = dbo_Aeries_Auxiliaries.ClubNum) ON dbo_Aeries_Auxiliaries_Type.Aeries_Auxiliaries_Type_ID = dbo_Aeries_Auxiliaries.Aeries_Auxiliaries_Type_ID) ON dbo_User.User_ID = dbo_Aeries_Auxiliaries.User_ID
WHERE (((dbo_Aeries_Auxiliaries.Aerie_Auxiliary_Status_ID)=IIf([TempVars]![View]=1,1) Or (dbo_Aeries_Auxiliaries.Aerie_Auxiliary_Status_ID)=IIf([TempVars]![View]=2,2) Or (dbo_Aeries_Auxiliaries.Aerie_Auxiliary_Status_ID)=IIf([TempVars]![View]=3,3) Or (dbo_Aeries_Auxiliaries.Aerie_Auxiliary_Status_ID)=IIf([TempVars]![View]=4,4) Or ((dbo_Aeries_Auxiliaries.Aerie_Auxiliary_Status_ID)=IIf([TempVars]![View]=5,1) Or (dbo_Aeries_Auxiliaries.Aerie_Auxiliary_Status_ID)=IIf([TempVars]![View]=5,2)) Or ((dbo_Aeries_Auxiliaries.Aerie_Auxiliary_Status_ID)=IIf([TempVars]![View]=6,3) Or (dbo_Aeries_Auxiliaries.Aerie_Auxiliary_Status_ID)=IIf([TempVars]![View]=6,4)) Or ((dbo_Aeries_Auxiliaries.Aerie_Auxiliary_Status_ID)=IIf([TempVars]![View]=7,1) Or (dbo_Aeries_Auxiliaries.Aerie_Auxiliary_Status_ID)=IIf([TempVars]![View]=7,2) Or (dbo_Aeries_Auxiliaries.Aerie_Auxiliary_Status_ID)=IIf([TempVars]![View]=7,3) Or (dbo_Aeries_Auxiliaries.Aerie_Auxiliary_Status_ID)=IIf([TempVars]![View]=7,4))) AND ((dbo_Aeries_Auxiliaries.User_ID) Like IIf([TempVars]![UserType_ID]=2,[TempVars]![User_ID],"*")))
GROUP BY dbo_Aeries_Auxiliaries.Aeries_Auxiliaries_ID, dbo_Aeries_Auxiliaries.ClubNum, dbo_Aeries_Auxiliaries.Aeries_Auxiliaries_Type_ID, dbo_Aeries_Auxiliaries_Type.Aeries_Auxiliaries_Type, IIf(Nz([ClubName],"z")="z",[Aeries_Auxiliaries_Type] & " " & [dbo_Clubs].[ClubNum] & " - " & [dbo_Clubs].[City] & ", " & [Abbreviation],[Aeries_Auxiliaries_Type] & " " & [dbo_Clubs].[ClubNum] & " - " & [ClubName] & " - " & [dbo_Clubs].[City] & ", " & [Abbreviation]), dbo_Aeries_Auxiliaries.Completed, [dbo_Clubs].[City] & " - " & [dbo_Clubs].[ClubNum], dbo_Clubs.City, dbo_States.State, dbo_Aeries_Auxiliaries.User_ID
ORDER BY IIf(Nz([ClubName],"z")="z",[Aeries_Auxiliaries_Type] & " " & [dbo_Clubs].[ClubNum] & " - " & [dbo_Clubs].[City] & ", " & [Abbreviation],[Aeries_Auxiliaries_Type] & " " & [dbo_Clubs].[ClubNum] & " - " & [ClubName] & " - " & [dbo_Clubs].[City] & ", " & [Abbreviation]);
Go to the top of the page
 
theDBguy
post Aug 19 2019, 11:58 AM
Post#8


Access Wiki and Forums Moderator
Posts: 76,290
Joined: 19-June 07
From: SunnySandyEggo


Hi. Glad to hear you got it sorted out. Good luck with your project.

--------------------
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 Aug 19 2019, 12:13 PM
Post#9



Posts: 659
Joined: 7-May 11



With out the help of you and the many other talented people out there non of this would be possible.

So thank you to you and the other unsung heroes out there.
Go to the top of the page
 
dale.fye
post Aug 19 2019, 04:33 PM
Post#10



Posts: 160
Joined: 28-March 18
From: Virginia


Still don't understand why the criteria that Leo (dbGuy) provided did not work in your query. That where clause is more efficient than using the LIKE criteria that you have used.

WHERE (UserType_ID=2 AND User_ID=TempVars!User_ID) OR UserType_ID=1

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th September 2019 - 07:57 PM