Parsing a variable length memo field

  • Thread starter Thread starter John_
  • Start date Start date
J

John_

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!
 
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.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
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],"."))
 
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!

John Spencer said:
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.

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

John_ said:
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!
Click to expand...
Click to expand...
 
Back
Top