Parsing a variable length memo field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John_ - 26 Nov 2007 19:47 GMT

I am importing a file into Access that contains one or more sentences. I am
trying to figure out a way to drop everything but the first sentence. The
problem is that the sentences are of differing lengths. Basically, I think I
want to drop everything past the first period that it finds. I don't know if
there is a command, group of commands or series of queries that can
accomplish this.

For example, one sentence reads:

" Line #: 6380: Agreement Number does not exist in the MRM valid agreements
file. If Agreement Number or its' formatting is not corrected an error will
occur when processed by MRM. This warning will not stop you from sending."

I want it to just pick up the following:

" Line #: 6380: Agreement Number does not exist in the MRM valid agreements
file."

Thanks for your help!

Reply to this Message

John Spencer - 26 Nov 2007 20:17 GMT

Use an expression like the following

Field: LEFT([Your Field], Instr(1,[Your Field} & "." ,"." ))

That should retrun everything up to and including the first period. If
there is no Period, it will return the entire string.

Signature

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

>I am importing a file into Access that contains one or more sentences. I
>am
[quoted text clipped - 21 lines]
>
> Thanks for your help!

Reply to this Message

John_ - 29 Nov 2007 15:51 GMT

Thanks John. That worked like a charm! There was one change I made to your
expression that I'm sure was an oversight and that was after the 2nd 'Your
Field' there was a '}' rather than a ']'. I only mention that because
someone else may have this same problem. Thanks so much for your help!

> Use an expression like the following
>
[quoted text clipped - 28 lines]
> >
> > Thanks for your help!

Reply to this Message

Daniel Pineault - 26 Nov 2007 20:21 GMT

What about using Instr() function to detect the position of the 1st period
and truncating the rest using the Left() function. Something like

Left([MemoField],Instr([MemoField],"."))

Signature

Hope this helps,

Daniel Pineault

> I am importing a file into Access that contains one or more sentences. I am
> trying to figure out a way to drop everything but the first sentence. The
[quoted text clipped - 15 lines]
>
> Thanks for your help!

Reply to this Message