Parse?

  • Thread starter Thread starter Lisa W.
  • Start date Start date
L

Lisa W.

I have a field that contains data as shown below:

Record 1
CN=McGhee\\, David M.,OU=C4A_OA-Dallas,OU=Users and Groups,OU=OIG,OU=LAN
Configs,DC=kc,DC=ad,DC=dhs,DC=gov

Record 2
CN=Williams\\, Teresa S.,OU=C4A_OA-Dallas,OU=Users and Groups,OU=OIG,OU=LAN
Configs,DC=kc,DC=ad,DC=dhs,DC=gov

I only need data beginning with "OU=C4A_OA-Dallas" in all records. How do I
tell Access all other data before and after this information?

Thanks in Advance!
 
I only need data beginning with "OU=C4A_OA-Dallas" in all records. How do I
tell Access all other data before and after this information?

Your phrasing lost me but here goes for returning all records that have
"OU=C4A_OA-Dallas" in them --
Like "*OU=C4A_OA-Dallas*"

This is to return all records that do not have "OU=C4A_OA-Dallas" in them --
Not Like "*OU=C4A_OA-Dallas*"
 
Karen,

Below was just an example because there can be several city names in a
file. I need to exclude all of the data before and after (example OU=
C4A_OA-Dallas or Tampa, or Baltimore, etc...) The file I'm working with has
over 2 thousand rows of data and the city names vary, therefore I need to
parse the information before and after "OU=C4A_OA-Dallas" as stated below.

Thanks!
 
I told you I did not understand what you were saying.
Do you want to extract data from the field like this --
OU=C4A_OA-Dallas
OU=C4A_OA-Tampa
OU=C4A_OA-Baltimore
Are do you want the records with this information removed?
Are do you only want records that have this in them?
Are do you want records that do not have this in them?
Are do you want the records with all text before OU=C4A_OA-Dallas removed?
 
Mid([YourField],Instr(1,[YourField],"OU=C4A_"))

will strip off everything prior to OU=C4A_

Instr(1,[YourField],"OU=Users") will determine the end point

So the following (all on one line) may give you what you want.
Mid([YourField],Instr(1,[YourField],"OU=C4A_"),
Instr(1,[YourField],"OU=Users")-Instr(1,[YourField],"OU=C4A_"))

I said may give you what you want because your request is unclear. IF you
can't figure out how to make it clearer in words. Include a couple samples of
what you start with and then what you want to end up with.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Thanks for the prompt response John. I will try this and let you know how it
works out.

Lisa W.

John Spencer said:
Mid([YourField],Instr(1,[YourField],"OU=C4A_"))

will strip off everything prior to OU=C4A_

Instr(1,[YourField],"OU=Users") will determine the end point

So the following (all on one line) may give you what you want.
Mid([YourField],Instr(1,[YourField],"OU=C4A_"),
Instr(1,[YourField],"OU=Users")-Instr(1,[YourField],"OU=C4A_"))

I said may give you what you want because your request is unclear. IF you
can't figure out how to make it clearer in words. Include a couple samples of
what you start with and then what you want to end up with.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I have a field that contains data as shown below:

Record 1
CN=McGhee\\, David M.,OU=C4A_OA-Dallas,OU=Users and Groups,OU=OIG,OU=LAN
Configs,DC=kc,DC=ad,DC=dhs,DC=gov

Record 2
CN=Williams\\, Teresa S.,OU=C4A_OA-Dallas,OU=Users and Groups,OU=OIG,OU=LAN
Configs,DC=kc,DC=ad,DC=dhs,DC=gov

I only need data beginning with "OU=C4A_OA-Dallas" in all records. How do I
tell Access all other data before and after this information?

Thanks in Advance!
Click to expand...
Click to expand...
 
John,

I tried your suggestion but get "null" values. When I try:

DN1: Mid([DN],InStr(1,[DN],"OU="),InStr(1,[DN],"OU"))

I get: "OU=C4A_OA-Dallas,OU=Use".
(Code) (City)

I only want to achieve "OU=C4A_OA-Dallas". There are numerous cities in the
table, so that's why I set the criteria above as field DN1. Again only want
to see "OU=Code-City. Everything after comma I want omitted.

Any additional help is appreciated.

Lisa W. said:
Thanks for the prompt response John. I will try this and let you know how it
works out.

Lisa W.

John Spencer said:
Mid([YourField],Instr(1,[YourField],"OU=C4A_"))

will strip off everything prior to OU=C4A_

Instr(1,[YourField],"OU=Users") will determine the end point

So the following (all on one line) may give you what you want.
Mid([YourField],Instr(1,[YourField],"OU=C4A_"),
Instr(1,[YourField],"OU=Users")-Instr(1,[YourField],"OU=C4A_"))

I said may give you what you want because your request is unclear. IF you
can't figure out how to make it clearer in words. Include a couple samples of
what you start with and then what you want to end up with.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I have a field that contains data as shown below:

Record 1
CN=McGhee\\, David M.,OU=C4A_OA-Dallas,OU=Users and Groups,OU=OIG,OU=LAN
Configs,DC=kc,DC=ad,DC=dhs,DC=gov

Record 2
CN=Williams\\, Teresa S.,OU=C4A_OA-Dallas,OU=Users and Groups,OU=OIG,OU=LAN
Configs,DC=kc,DC=ad,DC=dhs,DC=gov

I only need data beginning with "OU=C4A_OA-Dallas" in all records. How do I
tell Access all other data before and after this information?

Thanks in Advance!
Click to expand...
Click to expand...
Click to expand...
 
Try this --
DN1:
Mid([DN],InStr(1,[DN],"OU="),InStr(1,Right([DN],Len([DN])-InStr(1,[DN],"OU=")-1),"OU"))
--
KARL DEWEY
Build a little - Test a little


Lisa W. said:
John,

I tried your suggestion but get "null" values. When I try:

DN1: Mid([DN],InStr(1,[DN],"OU="),InStr(1,[DN],"OU"))

I get: "OU=C4A_OA-Dallas,OU=Use".
(Code) (City)

I only want to achieve "OU=C4A_OA-Dallas". There are numerous cities in the
table, so that's why I set the criteria above as field DN1. Again only want
to see "OU=Code-City. Everything after comma I want omitted.

Any additional help is appreciated.

Lisa W. said:
Thanks for the prompt response John. I will try this and let you know how it
works out.

Lisa W.

John Spencer said:
Mid([YourField],Instr(1,[YourField],"OU=C4A_"))

will strip off everything prior to OU=C4A_

Instr(1,[YourField],"OU=Users") will determine the end point

So the following (all on one line) may give you what you want.
Mid([YourField],Instr(1,[YourField],"OU=C4A_"),
Instr(1,[YourField],"OU=Users")-Instr(1,[YourField],"OU=C4A_"))

I said may give you what you want because your request is unclear. IF you
can't figure out how to make it clearer in words. Include a couple samples of
what you start with and then what you want to end up with.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Lisa W. wrote:
I have a field that contains data as shown below:

Record 1
CN=McGhee\\, David M.,OU=C4A_OA-Dallas,OU=Users and Groups,OU=OIG,OU=LAN
Configs,DC=kc,DC=ad,DC=dhs,DC=gov

Record 2
CN=Williams\\, Teresa S.,OU=C4A_OA-Dallas,OU=Users and Groups,OU=OIG,OU=LAN
Configs,DC=kc,DC=ad,DC=dhs,DC=gov

I only need data beginning with "OU=C4A_OA-Dallas" in all records. How do I
tell Access all other data before and after this information?

Thanks in Advance!
Click to expand...
Click to expand...
Click to expand...
 
Perfect!!! Thanks so much!!!

**John thanks to you as well. I wouldn't have gotten a start had it not
been for your assistance!!**

Lisa W.

KARL DEWEY said:
Try this --
DN1:
Mid([DN],InStr(1,[DN],"OU="),InStr(1,Right([DN],Len([DN])-InStr(1,[DN],"OU=")-1),"OU"))
--
KARL DEWEY
Build a little - Test a little


Lisa W. said:
John,

I tried your suggestion but get "null" values. When I try:

DN1: Mid([DN],InStr(1,[DN],"OU="),InStr(1,[DN],"OU"))

I get: "OU=C4A_OA-Dallas,OU=Use".
(Code) (City)

I only want to achieve "OU=C4A_OA-Dallas". There are numerous cities in the
table, so that's why I set the criteria above as field DN1. Again only want
to see "OU=Code-City. Everything after comma I want omitted.

Any additional help is appreciated.

Lisa W. said:
Thanks for the prompt response John. I will try this and let you know how it
works out.

Lisa W.

:

Mid([YourField],Instr(1,[YourField],"OU=C4A_"))

will strip off everything prior to OU=C4A_

Instr(1,[YourField],"OU=Users") will determine the end point

So the following (all on one line) may give you what you want.
Mid([YourField],Instr(1,[YourField],"OU=C4A_"),
Instr(1,[YourField],"OU=Users")-Instr(1,[YourField],"OU=C4A_"))

I said may give you what you want because your request is unclear. IF you
can't figure out how to make it clearer in words. Include a couple samples of
what you start with and then what you want to end up with.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Lisa W. wrote:
I have a field that contains data as shown below:

Record 1
CN=McGhee\\, David M.,OU=C4A_OA-Dallas,OU=Users and Groups,OU=OIG,OU=LAN
Configs,DC=kc,DC=ad,DC=dhs,DC=gov

Record 2
CN=Williams\\, Teresa S.,OU=C4A_OA-Dallas,OU=Users and Groups,OU=OIG,OU=LAN
Configs,DC=kc,DC=ad,DC=dhs,DC=gov

I only need data beginning with "OU=C4A_OA-Dallas" in all records. How do I
tell Access all other data before and after this information?

Thanks in Advance!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top