Returning Part of a Field

[Fritz24]

I have a datatable that contains employee logons in the format DomainName\LogonName. I need to write a query that will return just the LogonName part of the field, therefore everything right of the "\" but not including the "\"

Can anyone help please?

Thanks.

 

[Norie]

You could try the following expression:

Mid([DomainName\LogonName],InStr([DomainName\LogonName],"\")+1)

 

[Fritz24]

Just tried that and it's coming up with the Data Type Mismatch In Criteria Expression error.

Some of the records do not contain the "\" Could that be the problem? I'm not linking any tables and the field type is Text.

 

[Norie]

Does it work at all?

What data type is the field DomainName\LogonName?

How did you enter the formula?

 

[Fritz24]

Nope didn't work at all. I entered the formula as below:

Debt Manager ID : Mid([tbl_Members]![LogonName],InStr([tbl_Members]![LogonName],"\")+1)

The field type is text. As I said above not all of the entries contain the "\" and I don't know if that would make a difference or not. I ran a test query just using InStr([tbl_Members]![LognName],"\") to identify where the "\" appears and that worked fine.

 

[Norie]

I can't see where it's going wrong.

I did some testing and it worked fine for me.

 

[mdmilner]

The Mid function will return 0 if it doesn't find a valid match. You can read this in the built-in help, but it returns the position of first character in the string within the quotes "" that matches within the field.

So, no "\" and it returns 0.

You really have to find some consistent way to determine where the login begins in all records or this simply will not work. Look around for something...for example, all the file servers at my employer end with 'fs' -- TATL1111FS

Is there anything in the Domain names you could use?

Mike

 

[Norie]

So, no "\" and it returns 0.

Mike

That's why I have the +1 in the formula. If the '/' is not founf then the Instr will return 0. So the Mid function will start at the first character and return the full string.