Parse a field 1

[indyaries]

Access 97 SR1
Greetings,

I have a field that I'm trying to extract a portion of the text. I'm using this expression, but it's not returning what I need.

WC1: Trim(Mid([WC1],InStr(1,[WC1],"_&quot+1,IIf(InStr(InStr(1,[WC1],"_&quot+1,[WC1],"_&quot=0,0,InStr(InStr(1,[WC1],"_&quot+1,[WC1],"_&quot-InStr(1,[WC1],"_&quot)))

Here is a sample of the original data:
1_ADFGI_IOIO_AFL_5L2P_FY03

This is what is returned from the expression:
ADFGI_

This is what I need:
ADFGI

Suggestions appreciated !!

 

[scriverb]

1_ADFGI_IOIO_AFL_5L2P_FY03

WC1: Mid$([WC1], InStr(1, [WC1], "_&quot + 1, InStr(InStr(1, [WC1], "_&quot + 1, [WC1], "_&quot - InStr(1, [WC1], "_&quot)

The rules for selection of characters that I derived from your posting are:
Starting 1 character after the first underscrore through and including the second underscore.

If this is correct that the above column will work in your query.
Bob Scriver

 

[indyaries]

scriverb,

Close <smile>. I also want to exclude the underscore at the end of the data.

My goal will be to have the following returned:
ADFGI (No underscores).

The data I'm extracting from the field is the Organization Code. I'll then be comparing it to another field looking for errors.

Bob in Indy

 

[indyaries]

Hmmm.....I've now made it work !!

Expr1: Trim(Mid([WC1],InStr(1,[WC1],&quot;_&quot+1,IIf(InStr(InStr(1,[WC1],&quot;_&quot+1,[WC1],&quot;_&quot=0,0,InStr(InStr(1,[WC1],&quot;_&quot+1,[WC1],&quot;_&quot-InStr(1,[WC1],&quot;_&quot-1)))

By adding the -1 at the end of the expression, it removed the trailing underscore.

Thanks, though for making me think about it !!

Bob

 

[scriverb]

Boy, I misread that one. This will also work. Sorry.

WC1: Mid$([WC1], InStr(1, [WC1], &quot;_&quot + 1, InStr(InStr(1, [WC1], &quot;_&quot + 1, [WC1],&quot;_&quot - 1 - InStr(1, [WC1],&quot;_&quot)

Using the Mid$ and Instr functions only. Bob Scriver

 

[indyaries]

scriverb,

Thank you for your reply. After I implemented the expression, I've now learned of a new &quot;fly in the ointment&quot;.

I'm comparing the Organization that is in that long string of data to the Organization data in another field, as some employees are using the incorrect Org codes. In my comparisions, I had presumed that any non-match would be an &quot;error&quot;. I'm now told this isn't true.

Example:
WC1 = 1_ADFGIA_IOIO_AFL_5L2P_FY03
Org = ADFGI

When the data is given to me, the Org field originally has the first 5 characters of the Org, and then followed by: /IOIO. Hence, the Org code would appear: ADFGI/IOIO.

I've managed to remove the /IOIO part.
WC1 = 1_ADFGIA_IOIO_AFL_5L2P_FY03 would yield A.
What I need now is an expression that will extract JUST the last character of the Org in the WC1 field. I will then use that in my error-checking routines.

Thanks in advance !!

Bob in Indy

 

[scriverb]

This should do it:

WC1(SingleChar): Mid$([WC1], InStr(InStr(1, [WC1], &quot;_&quot + 1, [WC1],&quot;_&quot - 1,1)

Let me know if you need any adjustments.


Bob Scriver

 

[indyaries]

Bob,

Works like a charm! Thank you.

Now, where can I learn exactly the dirrerent elements of this expression, and what the difference is between MID and MID$.

Thanks Again !!

Bob in Indy

 

[scriverb]

Anytime you wish to see the definition of code just place your cursor behind the keyword like Mid$ and press your F1 key and ACCESS will take you to the Help Topic item for that code. Or, you can click on menu Item Help and select Contents and Index. Now select the Index tab and enter the code keyword or function that you are searching for. This will get you to the online definitions of ACCESS code.

As for the Mid vs the Mid$ functions and what are their differences:

Some functions have two versions: one that returns a Variant data type and one that returns a String data type. The Variant versions(Mid) are more convenient because variants handle conversions between different types of data automatically. They also allow Null to be propagated through an expression. The String versions(Mid$) are more efficient because they use less memory.

I hope this helps you in the future. Bob Scriver