A while back, Fritz helped me get a SQL query working to pull records from a dB and phenangle it to show either the user's assigned name or Unassigned if the record had no one assigned to it. That is working perfectly and is as below:
SQLStmt ="SELECT R.CreatedBy, R.ResearchID, R.AccountName, R.CurrentAccountNo, R.Status, R.RequestDate, R.DateAssigned, R.LocID, C.NickName + ' ' + C.LastName AS CreatedBy2, CASE (A.NickName + ' ' + A.LastName) WHEN ' ' THEN 'Unassigned' ELSE (A.NickName + ' ' + A.LastName) END AS AssignedTo FROM rrmain R INNER JOIN Employees C ON R.CreatedBy = C.EmployeeID LEFT OUTER JOIN Employees A ON R.AssignedTo = A.EmployeeID ORDER BY R.ResearchID"
Unfortunately, the group now wants only the records whose R.LocID matches the current user's LocationID (determined by a stored procedure, it's noted as Session("LocationID"). Further, If a user's Session("LocationID") is 34, that corresponds to a R.LocID of 50. The rest have matching numbers.
I've pulled up the various variables and they are all working correctly, but trying to get it to display just the records for the matching location outside of the SQL query has pulled a no records (despite the fact there are 4 records with a matching location to mine). Since I can't get that to work, I'm hoping someone here can help me modify the SQLStmt to pull only records where the R.LocID = Session("LocationID"), or if the Session("LocationID") is 34 show all records which have an R.LocID of 50. As always, any help is appreciated. Thanks in advance.
Start Free Trial
