467,877 Members | 1,295 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

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

MitchR
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.
4 Weeks Ago #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.

  • viewed: 1283
Share:
3 Replies
NeoPa
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.
4 Weeks Ago #2
MitchR
64KB
Thank you so much! I really appreciate your insight, and you are so right!
4 Weeks Ago #3
NeoPa
Expert Mod 16PB
Always a pleasure :-)
4 Weeks Ago #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 jack112 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.