parse data

[J. Grubb]

I'm trying to parse data in Access. Taking a portion of a description field
and putting it into a new field. I only want to grab the data up to the
first period (.) in the description field. Is there a way to do this?

 

[John Vinson]

I'm trying to parse data in Access. Taking a portion of a description field
and putting it into a new field. I only want to grab the data up to the
first period (.) in the description field. Is there a way to do this?

Yes:

Left([description], InStr([description], ".")-1)

This can be used as the UpdateTo expression in an Update query.

To get the part past the period, in case you need that to, use

Mid([description], InStr([description], ".") + 1)

John W. Vinson[MVP]

 

[J. Grubb]

Thanks for the quick answer, worked great. For me to better understand, what
is the -1 and +1 in the string?
--
Thanks,
J. Grubb

I'm trying to parse data in Access. Taking a portion of a description field
and putting it into a new field. I only want to grab the data up to the
first period (.) in the description field. Is there a way to do this?

Yes:

Left([description], InStr([description], ".")-1)

This can be used as the UpdateTo expression in an Update query.

To get the part past the period, in case you need that to, use

Mid([description], InStr([description], ".") + 1)

John W. Vinson[MVP]

 

[John Vinson]

Thanks for the quick answer, worked great. For me to better understand, what
is the -1 and +1 in the string?

InStr returns the position of the period. For instance,

InStr("abcdef.ghijk", ".")

will return 7, the number of characters into the string where the
first period is found.

If you want to return "abcdef" you need to return the leftmost six
characters, *not* including the period - so you subtract 1 from the
position of the period.

Similarly, you'ld add 1 to start at the 8th byte, the letter g. The
Mid() function returns a substring; you can use

Mid(<string>, <start>, <length>)

to return a selected number (<length>) of characters beginning at
<start>. If you leave off the optional length parameter, it returns
from <start> to the end of the string.


John W. Vinson[MVP]

 

[Vicki-S]

This is ALMOST what I'm looking for - I need to strip off the END of a string:

Sample of Data can be variable length (memo field):

CNV DFLT: MOVE 0
or
CNV DFLT: MOVE 'XYZ123ABC456'

All begin with "CNV DFLT: " - I need to move the text after "CNV DFLT: " to
another field.

Can this be done? I'm using Access 2002-2003. Thanks again for your prompt
assistance.

 

[Vicki-S]

Please disregard my previous post - I can see where you already answered my
questions. Thanks!!!

 

[jhicsupt]

I get an Error when there is no ".".

If field is blank, how do I just return an empty/blank field if there is no
".".

I'm trying to parse data in Access. Taking a portion of a description field
and putting it into a new field. I only want to grab the data up to the
first period (.) in the description field. Is there a way to do this?

Yes:

Left([description], InStr([description], ".")-1)

This can be used as the UpdateTo expression in an Update query.

To get the part past the period, in case you need that to, use

Mid([description], InStr([description], ".") + 1)

John W. Vinson[MVP]

 

[PieterLinden via AccessMonster.com]

I get an Error when there is no ".".

If field is blank, how do I just return an empty/blank field if there is no
".".

So test for null first, then if not null, do the parse.

IIF(Not IsNull(MyField), f(x), Null)
or
IIF(IsNull(MyField),Null, f(x))
... too many double negatives will make you crazy...

 

[John Spencer]

Change the expression to ensure that a period is detected by the Instr call by
appending a period to the end of description.

Left([description], InStr([description] & ".", ".")-1)

Or if you only want description in there is a period you can change that to
IIF([Description] Like "*.*",Left([description], InStr([description],".")-1),Null)

That will give you null for all the following
This is a description with no period
""
Null


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I get an Error when there is no ".".

If field is blank, how do I just return an empty/blank field if there is no
".".

I'm trying to parse data in Access. Taking a portion of a description field
and putting it into a new field. I only want to grab the data up to the
first period (.) in the description field. Is there a way to do this?

Yes:

Left([description], InStr([description], ".")-1)

This can be used as the UpdateTo expression in an Update query.

To get the part past the period, in case you need that to, use

Mid([description], InStr([description], ".") + 1)

John W. Vinson[MVP]