Results 1 to 5 of 5
  1. #1
    whisp0214 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    May 2017
    Posts
    30

    Query Based on Username

    I currently have a query that pulls information based on the user that is logged in. What I would like to do is if I am the user, I see all of the data. I can see it anyway through other means, but i would like to fix the SQL so it automatically does it. I have tried IIF statements with no luck. Basically, if I am the user, the Where statement would not exist. If I am not the user, then the where statement would exist. My SQL language is below.

    Code:
    SELECT tblPurchaseOrders.[ID], tblPurchaseOrders.[Sales ID], tblPurchaseOrders.[Assistant ID], tblPurchaseOrders.[Vendor ID], tblPurchaseOrders.[Retailer ID], tblPurchaseOrders.[PO Number], tblPurchaseOrders.[Amount], tblPurchaseOrders.[Due Date], tblPurchaseOrders.[Paid]
    
    
    FROM tblAssistants INNER JOIN (tblsalesperson INNER JOIN tblPurchaseOrders ON tblsalesperson.[Sales ID] = tblPurchaseOrders.[Sales ID]) ON tblAssistants.[Assistant ID] = tblPurchaseOrders.[Assistant ID]
    
    
    WHERE (((tblsalesperson.UserName)=TempVars!Username)) Or (((tblAssistants.UserName)=TempVars!Username));
    Garret

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Copy this into a new query, then in query design view:
    - first line of criteria: if table user = login user (as you have it above)
    - second line of criteria (this is for OR): if table user LIKE "*" AND login user = me

  3. #3
    whisp0214 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    May 2017
    Posts
    30
    I appreciate the advice, but this does not work in my case. When I add your second line to the OR section, the assistants can't view any orders with their username. The Sales Person and Assistants are pulled from separate tables so that is why I had the query set up the way it was. I may need to rework the design of the tables, if there is no easy way to query it as is.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    That doesn't make sense. The assistants can view the orders based on the first line. The error must be somewhere else.

    Write it out in a sentence: if user = login or (user = anything and login = me) then show all the records

  5. #5
    whisp0214 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    May 2017
    Posts
    30
    My issue is that I was cross referencing the Salesperson Username and the Assistant's Username on two separate tables, which is why i had the criteria as either or. When added your line to the query it would turn an OR into an AND and then it wouldn't work. I got it working with another or statement. If that is what you meant all along, sorry for the confusion. I appreciate the help, as it did steer me in the right direction.

    Code:
    WHERE (((tblsalesperson.UserName)=TempVars!Username)) Or (((tblAssistants.UserName)=TempVars!Username)) Or (((TempVars!Username) Like "*" And (TempVars!Username)="My Name"));

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to create a view based on username
    By satz in forum Access
    Replies: 0
    Last Post: 06-22-2016, 02:49 AM
  2. Running a Query based upon UserName
    By Robert2150 in forum Queries
    Replies: 1
    Last Post: 05-04-2016, 09:37 AM
  3. Replies: 14
    Last Post: 08-17-2015, 02:32 AM
  4. Replies: 1
    Last Post: 02-17-2013, 04:46 PM
  5. Message popup based on USERNAME
    By jpkeller55 in forum Access
    Replies: 3
    Last Post: 12-19-2010, 09:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums