Access Query Criteria - working with a Domain "\" user list.

[MitchR]

Howdy Folks!

I am working on a project where I have been given Domain\Usernames (Contoso\JSmith) and my data dump has records containing the Domain\ and no user name, Domain\administrator, and Bogus Domains that I need to remove with delete queries. I am looking to isolate both the left and right of the "\" to remove garbage items, but I need some assistance in doing so. I have attempted to use the following:

Right([tbl_users].[user_list_Column],InStr([tbl_users].[user_list_Column],'\'))>"" 

Left([US_Local_Admin],InStr([US_Local_Admin],'\')-1) <>"Contoso"

I know I am going nowhere fast and need some assistance.

[NeoPa]

Hi Mitch.

It can be done all in one go but it's easier to define two (calculated) Fields first then reference them in your logic :

Domain: IIf([tbl_users].[user_list_Column] Is Null,Null,Left([tbl_users].[user_list_Column],InStr([tbl_users].[user_list_Column],'\')-1))
UserName: IIf([tbl_users].[user_list_Column] Is Null,Null,Mid([tbl_users].[user_list_Column],InStr([tbl_users].[user_list_Column],'\')+1,999))
Check: ([Domain]<>'Contoso')

Etc.

[MitchR]

Thank you so much! I really appreciate your insight, and you are so right!

[NeoPa]

Always a pleasure :-)