470,822 Members | 1,369 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,822 developers. It's quick & easy.

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

MitchR
65 64KB
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:
Expand|Select|Wrap|Line Numbers
  1. Right([tbl_users].[user_list_Column],InStr([tbl_users].[user_list_Column],'\'))>"" 
Expand|Select|Wrap|Line Numbers
  1. Left([US_Local_Admin],InStr([US_Local_Admin],'\')-1) <>"Contoso"
I know I am going nowhere fast and need some assistance.
Jan 27 '21 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. Domain: IIf([tbl_users].[user_list_Column] Is Null,Null,Left([tbl_users].[user_list_Column],InStr([tbl_users].[user_list_Column],'\')-1))
  2. UserName: IIf([tbl_users].[user_list_Column] Is Null,Null,Mid([tbl_users].[user_list_Column],InStr([tbl_users].[user_list_Column],'\')+1,999))
  3. Check: ([Domain]<>'Contoso')
Etc.

3 1466
NeoPa
32,311 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Domain: IIf([tbl_users].[user_list_Column] Is Null,Null,Left([tbl_users].[user_list_Column],InStr([tbl_users].[user_list_Column],'\')-1))
  2. UserName: IIf([tbl_users].[user_list_Column] Is Null,Null,Mid([tbl_users].[user_list_Column],InStr([tbl_users].[user_list_Column],'\')+1,999))
  3. Check: ([Domain]<>'Contoso')
Etc.
Jan 28 '21 #2
MitchR
65 64KB
Thank you so much! I really appreciate your insight, and you are so right!
Jan 29 '21 #3
NeoPa
32,311 Expert Mod 16PB
Always a pleasure :-)
Jan 30 '21 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by jen | last post: by
2 posts views Thread by Aussie Rules | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.