Report Query not working as expected

[mjbliss]

Hi,

All my user accounts in AD have the description "User Account" set, this is to differentiate them from Consulting Accounts and Service Accounts, they also have "Company" populated under the Company field.

I'm trying to create a query that lets me know which accounts do not have Employee ID populated, I thought the below query would do the trick but the results still show users that have something other than "User Account" in the description field.

The filtering of users that DO HAVE employee ID populated works as expected.

Any reason why the And tblADusers.Description = 'User Account') doesn't seems to work?

Select Top 1000000 tblADusers.Username,
tblADusers.Userdomain,
tblADusers.Office,
tblADusers.EmployeeID,
tblADusers.Company,
tblADusers.Description
From tblADusers
Where (tblADusers.EmployeeID Like '' And tblADusers.Company = 'Company' And
tblADusers.Description = 'User Account') Or
(tblADusers.EmployeeID Is Null)

[Esben_D]

Looking at your where clause I can see the problem.

You're basically displaying two groups of users in your query:

1. EmployeeID is empty, company is 'company' and description is 'User Account'
   
2. EmployeeID is NULL

This means that if a user has NULL for EmployeeID, it will be displayed regardless of any other fields.

I would use the following for your situation:

Where (tblADusers.EmployeeID Like '' And tblADusers.Company = 'Company' And
tblADusers.Description = 'User Account') Or
(tblADusers.EmployeeID Is Null And tblADusers.Company = 'Company' And
tblADusers.Description = 'User Account')

Edit: Seems like you figured it out while I was posting 😉

[mjbliss]

I checked this morning and somehow a consultant name appeared on my list, so I didnt quite have it. Ive replaced my code with yours and its working perfectly now. Thank you.

Charles.X wrote:
Looking at your where clause I can see the problem.

You're basically displaying two groups of users in your query:

1. EmployeeID is empty, company is 'company' and description is 'User Account'
   
2. EmployeeID is NULL

This means that if a user has NULL for EmployeeID, it will be displayed regardless of any other fields.

I would use the following for your situation:

Where (tblADusers.EmployeeID Like '' And tblADusers.Company = 'Company' And
tblADusers.Description = 'User Account') Or
(tblADusers.EmployeeID Is Null And tblADusers.Company = 'Company' And
tblADusers.Description = 'User Account')

Edit: Seems like you figured it out while I was posting 😉

[mjbliss]

This seems to have done the trick;

Select Top 1000000 tblADusers.Username,
tblADusers.Userdomain,
tblADusers.Office,
tblADusers.EmployeeID,
tblADusers.Company,
tblADusers.Description
From tblADusers
Where (tblADusers.EmployeeID Like '' And tblADusers.Company = 'Ugu' And
tblADusers.Description = 'User Account') Or
(tblADusers.EmployeeID Is Null And tblADusers.Description = 'User Account')

mjbliss wrote:
Hi,

All my user accounts in AD have the description "User Account" set, this is to differentiate them from Consulting Accounts and Service Accounts, they also have "Company" populated under the Company field.

I'm trying to create a query that lets me know which accounts do not have Employee ID populated, I thought the below query would do the trick but the results still show users that have something other than "User Account" in the description field.

The filtering of users that DO HAVE employee ID populated works as expected.

Any reason why the And tblADusers.Description = 'User Account') doesn't seems to work?

Select Top 1000000 tblADusers.Username,
tblADusers.Userdomain,
tblADusers.Office,
tblADusers.EmployeeID,
tblADusers.Company,
tblADusers.Description
From tblADusers
Where (tblADusers.EmployeeID Like '' And tblADusers.Company = 'Company' And
tblADusers.Description = 'User Account') Or
(tblADusers.EmployeeID Is Null)