Solved Select Query not returning "all" (1 Viewer)

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:59
Joined
Apr 1, 2019
Messages
713
People, this may sound silly. I have a select query that I wish to return the records for either that user (if level 1) or all users (as for a supervisor) if not level 1. I've tried various permutations of

IIf(getuserlevel()=1,getusername(),"*") in the select criteria of the "Username" field. It works for user level 1 and returns those records, but will not return any records if user level is not =1 (as in a "supervisors"security.

Both GetUserLevel() & GetUserName() are subroutines that work elsewhere.

What's wrong?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:59
Joined
Oct 29, 2018
Messages
21,358
Maybe you could try it this way.

Code:
(GetUserLevel()=1 AND GetUserName()) OR GetUserLevel()<>1
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:59
Joined
Apr 1, 2019
Messages
713
@theDBguy Thanks. Will give it a go.

Did, but displays all records.. If I use GetUserName() alone it returns the correct records for that user. I just want the Administrator to be able to see all records. (PS - I'm using your excellent log-in code that I've used on several of my applications- Really appreciate your generosity with that)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:59
Joined
Oct 29, 2018
Messages
21,358
@theDBguy Thanks. Will give it a go.

Did, but displays all records.. If I use GetUserName() alone it returns the correct records for that user. I just want the Administrator to be able to see all records. (PS - I'm using your excellent log-in code that I've used on several of my applications- Really appreciate your generosity with that)
Would you mind showing exactly how you tried my suggestion to make sure there wasn't any misunderstanding?
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:59
Joined
Apr 1, 2019
Messages
713
@theDBguy, sure.

I have a select Query :
SELECT QuotesTBL.QUOTEID, QuotesTBL.JimCode, QuotesTBL.FreightID, QuotesTBL.QuoteValidity, QuotesTBL.[Delivery Location], QuotesTBL.Delivery_Address, QuotesTBL.CustcontactID, QuotesTBL.Date_Quoted, QuotesTBL.Quote_Description, QuotesTBL.Quoted_Freight, QuotesTBL.Cost_RM_STD, QuotesTBL.GP_Percentage, QuotesTBL.Cost_RM_LS, QuotesTBL.Cost_RM_Clr, QuotesTBL.Cost_MFG, QuotesTBL.Sell_RM_STD, QuotesTBL.Sell_RM_LS, QuotesTBL.Cost_RM_HV, QuotesTBL.EmployeeRef, QuotesTBL.Quote_Status, QuotesTBL.Quote_Status_Date, QuotesTBL.Cust_Order, QuotesTBL.Pronto_Sales_Order, QuotesTBL.[Quote_Accepted(Y/N)], TBLFreight.Freight_Rate, CompaniesTBL.Rebate_Percentage, CompaniesTBL.[Quote_Validity(Days)], QuotesTBL.Cost_RM_Stripe, QuotesTBL.Cost_RM_BLK, QuotesTBL.[AIM_GP ($)]
FROM TBLFreight INNER JOIN (CompaniesTBL INNER JOIN QuotesTBL ON CompaniesTBL.JimCode = QuotesTBL.JimCode) ON TBLFreight.FreightID = QuotesTBL.FreightID
WHERE (((QuotesTBL.EmployeeRef)=GetUserName()) AND ((GetUserLevel())=1)) OR (((GetUserLevel())<>"1"))
ORDER BY QuotesTBL.QUOTEID;


So I put your line in the criteria below EmployeeRef. I see the query made a new column 'GetUserLevel() with Criteria 1 or <>"1"?

I guess this says it all?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:59
Joined
Oct 29, 2018
Messages
21,358
Hi. Thanks. Why is the last 1 enclosed in quotes? Try removing them. Should be <>1, not <>"1".
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:59
Joined
Apr 1, 2019
Messages
713
@theDBguy , can still scroll through all records;

SELECT QuotesTBL.QUOTEID, QuotesTBL.JimCode, QuotesTBL.FreightID, QuotesTBL.QuoteValidity, QuotesTBL.[Delivery Location], QuotesTBL.Delivery_Address, QuotesTBL.CustcontactID, QuotesTBL.Date_Quoted, QuotesTBL.Quote_Description, QuotesTBL.Quoted_Freight, QuotesTBL.Cost_RM_STD, QuotesTBL.GP_Percentage, QuotesTBL.Cost_RM_LS, QuotesTBL.Cost_RM_Clr, QuotesTBL.Cost_MFG, QuotesTBL.Sell_RM_STD, QuotesTBL.Sell_RM_LS, QuotesTBL.Cost_RM_HV, QuotesTBL.EmployeeRef, QuotesTBL.Quote_Status, QuotesTBL.Quote_Status_Date, QuotesTBL.Cust_Order, QuotesTBL.Pronto_Sales_Order, QuotesTBL.[Quote_Accepted(Y/N)], TBLFreight.Freight_Rate, CompaniesTBL.Rebate_Percentage, CompaniesTBL.[Quote_Validity(Days)], QuotesTBL.Cost_RM_Stripe, QuotesTBL.Cost_RM_BLK, QuotesTBL.[AIM_GP ($)]
FROM TBLFreight INNER JOIN (CompaniesTBL INNER JOIN QuotesTBL ON CompaniesTBL.JimCode = QuotesTBL.JimCode) ON TBLFreight.FreightID = QuotesTBL.FreightID
WHERE (((QuotesTBL.EmployeeRef)=GetUserName()) AND ((GetUserLevel())=1)) OR (((GetUserLevel())<>1))
ORDER BY QuotesTBL.QUOTEID;

Appreciate it though.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:59
Joined
Oct 29, 2018
Messages
21,358
Can you verify what GetUserLevel() returns?
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:59
Joined
Apr 1, 2019
Messages
713
So I tried it in the immediate window. User = 1 ,Developer =3
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:59
Joined
Feb 19, 2002
Messages
42,981
WHERE (QuotesTBL.EmployeeRef = GetUserName() AND GetUserLevel() =1) OR GetUserLevel() <> 1
I removed the extraneous parentheses so you can see the only set that is important. The problem though ws that the 1 was enclosedin "" in the OR part of the condition
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:59
Joined
Apr 1, 2019
Messages
713
Pat, as usual I owe you one. Thanks & I'll let you know how I go. Won't probably be for a few days. I've had enough in front of the computer for a bit. Cheers
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:59
Joined
Apr 1, 2019
Messages
713
Pat, Works a treat. THanks.
 

Users who are viewing this thread

Top Bottom